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

The answer here may help: https://stackoverflow.com/questions/63684996/call-jsonb-contains-function-postgres-using-jpa-criteria-and-jsonbinarytype/72679167 — Evgeny Umansky, Jul 07 '22 at 15:28

score 1 · Answer 1 · answered Nov 13 '16 at 21:23

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

score 0 · Answer 2 · answered Sep 25 '17 at 11:59

Working piece of code with prepare statement:

WhereObj whereObj = new WhereObj();
StringBuilder where = new StringBuilder(" where ");
...
// inside cycle
where.append(" file_object\\:\\:jsonb @> ?\\:\\:jsonb ");
whereObj.params.add(jsonStr);
...
whereObj.sql = where.toString();

Usage later:

    Query nativeQuery = em.createNativeQuery(
       "select * from file_object f " 
          + whereObj.sql + sortSQL, 
        FileObjectEntity.class
    );
    setParameters(nativeQuery, whereObj);
    List<FileObjectEntity> resultList = nativeQuery.getResultList();

Method setParameters:

private void setParameters(Query nativeQuery, WhereObj whereObj) {
    for (int i=0, n=whereObj.params.size(); i<n; i++) {
        nativeQuery.setParameter(i + 1, whereObj.params.get(i));
    }
}


private class WhereObj {
    private String sql = StringUtils.EMPTY;
    private ArrayList<String> params = new ArrayList<>();
}

Postgres jsonb with Hibernate

2 Answers2