21

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

Improve this question
7
  • JPA doesn't support such things, as a quick revision of the JPA spec or any implementations docs would say. Some specific type in some specific RDBMS is likely never to be standardised since it is just that ... specific Commented Feb 4, 2016 at 18:11
  • I've found this source code based on eclipselink: github.com/sasa7812/psql-cache-evict-POC/blob/master/src/test/… , but it didn't work on hibernate. Commented Feb 5, 2016 at 19:28
  • errm yes, and as i said, IT IS NOT STANDARD. So it may work on 1 provider but not on any other provider. So you cannot rely on it for portable applications. Commented Feb 5, 2016 at 19:34
  • 3
    Proof of Concept project added here : github.com/mariusneo/postgres-json-jpa if somebody else is interested on this topic. Commented Mar 4, 2016 at 8:00
  • 3
    In case you're using Hibernate as your JPA implementation, you may want to read How to map JSON objects using generic Hibernate Types. Commented May 9, 2017 at 17:41

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Since there is no JPA support for "JSON" fields as such in JPA then there is no support for querying them. JPA allows a user to use SQL and hence make use of very specific column types, and also the associated query methods. That is then RDBMS-specific. Can't imagine that will ever get in any JPA spec, but could be supported by a JPA provider as a vendor extension

Improve this answer

Comments

0

The point of a standard like JPA is to have a common set of features supported across all DB vendors. As querying on JSON fields is rather a vendor specific feature, in this case Postgres, it is hard to expect it to find it's implementation in JPA until it is considered to be a standard feature of all relational SQL databases.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.