JSON key search in PostgreSQL using Hibernate

Question

I have a JSON field data that contains following data-

{"name":"xx"}

I want to perform a full text search on this field using Hibernate. Is there any way I can achieve this? I followed some examples but nothing worked for me.

Answer 1

Ideally Hibernate would have native support for querying inside JSON objects via JPQL/HQL and/or Criteria queries. That does not appear to be the case at the moment (5.0.1).

It looks like at present you will need to use native queries using one of these methods:

• Use EntityManager.createNativeQuery or one of its variants;

• Define a @NamedNativeQuery in your mapping then use EntityManager.createNamedQuery to run it; or

• Unwrap the EntityManager, unwrapping the Session underneath, and use JDBC

If you're using the native Hibernate interfaces not the JPA ones there are Hibernate equivalents to all the above - named native queries in the mapping, creating native queries via the Session object, or unwrapping the Session to get a java.sql.Connection.

In all those cases you'll need to use PostgreSQL-specific operators like ? and ->> for querying the json and regular SQL, not JPQL/HQL. See postgresql json operators. Note that JSON functionality was introduced in 9.3 and enhanced in both 9.4 and 9.5, so check whether your version has the features you need.

For example, you can use myjsoncolumn ? 'key' with jsonb in 9.4+. With plain json types, you'll need to use (myjsoncolumn -> 'key') IS NULL. It's important to use -> not ->> because -> returns a json null object for an object like {"a":null}, the other returns NULL, so you can't tell the difference between {"a":null} and {}.