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

Paolo Melchiorre
  • 5,716
  • 1
  • 33
  • 52
marius_neo
  • 1,535
  • 1
  • 13
  • 28
  • 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 –  Feb 04 '16 at 18:11
  • I've found this source code based on eclipselink: https://github.com/sasa7812/psql-cache-evict-POC/blob/master/src/test/java/ru/savvy/service/JsonMappingIntegrationTest.java , but it didn't work on hibernate. – marius_neo Feb 05 '16 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. –  Feb 05 '16 at 19:34
  • 3
    Proof of Concept project added here : https://github.com/mariusneo/postgres-json-jpa if somebody else is interested on this topic. – marius_neo Mar 04 '16 at 08: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](https://vladmihalcea.com/2016/06/20/how-to-map-json-objects-using-generic-hibernate-types/). – Edwin Dalorzo May 09 '17 at 17:41
  • @BillyFrost maybe you should post your comment as an answer (because it is an answer to the question) so people that visit this question can easily find it. – Dimitar Spasovski Dec 15 '18 at 16:47
  • JPA/JPGL confines you to a 10+ years obsolete and crippled subset of SQL. It is not a standard too. Why shall you use it at all if you _can_ write decent SQL proper? Maybe the solution of your problem is [here](https://thorben-janssen.com/jpa-native-queries/) – Stefanov.sm Nov 30 '20 at 19:33

3 Answers3

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.

yodacola
  • 21
  • 4
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

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.

rastov
  • 104
  • 5