CODEWITHSUNDEEP
javahibernatepostgresqlfull-text-search
Justin
Justin

Reputation: 745

JSON key search in PostgreSQL using Hibernate

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.

Upvotes: 5

Views: 4459

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324485

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:

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 {}.

Upvotes: 3

Related Questions