[FIXED] hibernate wrongly converts query for jsonb field

Issue

I have a jsonb field in which I have stored a key , value mapping in the db. Not I want to make a query where given a text I want to know if a key exist which is like the text. example the key in db is 'abc_def' and I have the text 'abc' so the query should return the record.

I have written a query for it:

String sql = select * from table where (attrs->>'data')::jsonb::text like :queryKeyEspCorpCompany and status = 'ENABLED'

queryKeyEspCorpCompany is passed a a param. but when hibernate actually run the query it returns like so (in logs) and return error:

select * from table where '(attrs->>'data')':jsonb:text like ? and status = 'ENABLED'

error is where the "::" before jsonb is getting converted to ":".

Can someone help why this is happening ?

Solution

The character : needs to be escaped:

String sql = "select * from table where (attrs->>'data')\\:\\:jsonb\\:\\:text like ?1 and status = 'ENABLED'";

Because it's a native query, you have to use Postgres parameter placements syntax: ?1, ?2,...

Example:

session.createSQLQuery(sql).setParameter(1, "abc_def").getResultList();

Answered By - Davide