Postgres jsonb with Hibernate

Question

I have the below Postgres query which works fine when run through the psql client -

select id,jbag 
from mydb.mytable e 
where (e.jbag->'myCodes')::jsonb @> '{"C":"C", "T":"T", "L":"L"}';

However, when I run the query by creating a org.Hibernate.query object, I get an exception similar to this -

org.postgresql.util.PSQLException: ERROR: operator does not exist: jsonb @> character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 144

Please help. I have read the following post about creating a new type and that Hibernate does not support postgres JSON operators -

http://www.thoughts-on-java.org/persist-postgresqls-jsonb-data-type-hibernate/

But is there a simple solution?

Thanks

Answer 1

I solved this problem using json_build_object operator in postgres to construct a JSON object and use it in a query like this -

select id,jbag 
from mydb.mytable e 
where (e.jbag->'myCodes')\\:\\:jsonb @> json_build_object(:jsonStr)\\:\\:jsonb;

I stored the above query string in a Java StringBuffer object something like this -

StringBuilder buf = new StringBuilder(); buf.append("SELECT.....

......append("(e.jbag->'myCodes')\:\:jsonb @> json_build_object(:jsonStr)\:\:jsonb ")

where jsonStr is a Java String object defined like this -

String jsonStr = new String("'C', 'C', 'T', 'T'");

Pardon me for not posting the full query - my actual query is quite long. Note the double \ used to escape the double colon

Then used the Hibernate setParameter method to set the object in the query -

query2.setParameter("jsonStr", jsonStr);

where query2 is my org.Hibernate.Session object and finally called the list method on the query object -

List<String> statusCodeList = query2.list();

Below is the postgres documentation page about operators -

https://www.postgresql.org/docs/9.4/static/functions-json.html