Using Oracle SQL-Function JSON_EXISTS in JPQL

Question

In my Oracle-database table mytable I have a column columnx with JSON-Arrays (VARCHAR2) and I would like to find all entries where the value valueX is inside that array.

In native Oracle-SQL the following query is working very well: SELECT * FROM mytable t WHERE JSON_EXISTS(columnx, '$?(@ == "valueX")');

In my Spring Boot Application I write queries in JPQL, so I have to convert it.

The following queries were unsuccessful:

1. I found out that I have to use 'FUNCTION()' for specific SQL-Oracle-functions: @Query(value = "SELECT t FROM mytable t WHERE FUNCTION('JSON_EXISTS',t.columnx, '$?(@ == \"valueX\")')") That results in a JPQL-Parsing-Error: "QuerySyntaxException: unexpected AST node: function (JSON_EXISTS)"

2. I found out that JPQL needs a real boolean-comparison, so I tried this: @Query(value = "SELECT t FROM mytable t WHERE FUNCTION('JSON_EXISTS',t.columnx, '$?(@ == \"valueX\")') = TRUE") Now the JPQL-Converter can parse it to native SQL successfully, but I got an Oracle-Error while executing the query: "ORA-00933: SQL command not properly ended." That's understandable since the parsed native ... WHERE JSON_EXISTS(columnx, '$?(@ == "valueX")') = 1 won't run either.

What is the right way to solve this problem? Do you have any idea?

EDIT: This is the field definition in JAVA:

@TypeDef(name = "json", typeClass = JsonType.class)
public class Mytable {
    ...
    @NotNull
    @Type(type = "json")
    @Column(columnDefinition = "VARCHAR2")
    private List<String> columnx;

Answer 1

you can try the below using native query.

@Query(value = "SELECT * FROM mytable t WHERE 
JSON_EXISTS(columnx, '$?(@ == \"valueX\")')", 
nativeQuery = true)

OR You can hide the JSON_EXISTS implementation inside a view in oracle and call the view in JPQL.

    create or replace view my_table_json_exists as SELECT * FROM 
    mytable t WHERE 
    JSON_EXISTS(t.columnx, '$?(@ == "valueX")');

   @Query(value ="select t from my_table_json_exists t");

If you are only looking for valueX inside a json key then you can explore JSON_VALUE.

Answer 2

In JPQL use MEMBER OF operator to check if a value is contained in a JSON array stored in a column. The JPQL query would look something like this:

@Query("SELECT t FROM mytable t WHERE :valueX MEMBER OF t.columnx")
List<Mytable> findByValueX(@Param("valueX") String valueX);