CODEWITHSUNDEEP
javaoracle-databasehibernatejpa
George
George

Reputation: 3030

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?

Upvotes: 3

Views: 2925

Answers (2)

Prasannjeet Singh
Prasannjeet Singh

Reputation: 1447

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

Upvotes: 0

Stefan Zhelyazkov
Stefan Zhelyazkov

Reputation: 2981

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);

Upvotes: 2

Related Questions