[FIXED] JPA Querying for json field in Oracle DB

Issue

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?

Solution

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

Answered By - Stefan Zhelyazkov
Answer Checked By - Willingham (JavaFixing Volunteer)