'JPA Querying for json field in Oracle DB

I have a JPA Entity as such:

import com.fasterxml.jackson.databind.JsonNode; 

@Entity
public class Data {
  @Id
  private Integer id;
  @Lob
  private JsonNode json;
}

Is it possible to write JPQL to query the json field values?

Something like this:

from Data d where d.json.firstName = :firstName

I'm getting this error:

QueryException: could not resolve property: firstName

Is it possible to configure it to ignore property resolution errors when accessing JsonNode fields?

javaoraclehibernatejpa


Solution 1:[1]

In general, you might be able to perform a string match in the WHERE clause. More specific JSON search needs to be supported by the database engine. I found this documentation for Oracle DB: https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6293 Something like this might work, but still check the documentation:

SELECT * 
FROM Data d
WHERE json_textcontains(d.json, '$.firstName', firstName);

Solution 2:[2]

There is a package called oracle.sql.json that does exactly that. You might want to take a look at: https://docs.oracle.com/en/database/oracle/oracle-database/21/jajdb/oracle/sql/json/package-summary.html

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stefan Zhelyazkov
Solution 2 misteeque

Related Questions