JPA support for querying Postgres JSON fields

Question

Is there already support in JPA for dealing with queries on JSON fields like the following :

select * from person where (info ->> 'age')::numeric = 40;

select * from person where info ->> 'firstName'= 'Cabalo';

I'm using hibernate 5 (behind spring-data-jpa) and Postgres 9.4

Answer 1

JPA doesn't natively support converting JSON or JSONB fields. You will need to create an javax.persistence.AttributeConverter in order to to/from JSON/JSONB. Another important class to remember is org.postgresql.util.PGobject , which is useful for converting to/fromjsonb` fields.

Secondly, JPA is only an API. You will need to customize your underlying implementation in order to fully take advantage of this type. You will need to create org.hibernate.dialect.function.SQLFunction in order to use JSON/JSONB functions. You will need to register this along with your created AttributeConverter types in org.hibernate.dialect.PostgreSQL95Dialect.

Another note: it may be beneficial to refer to the json/jsonb operators you are using as native function calls or as an alias when creating SQLFunction. There is a tendency for native queries containing a ? operator to get mangled even when it is properly escaped.

See Hibernate Javadocs and JPA Javadocs.