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:

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

psaraj12 · Accepted Answer · 2022-12-02 22:40:37Z

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.

edited Dec 2, 2022 at 22:40

answered Nov 29, 2022 at 16:09

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mp9500 Over a year ago

Thanks for your answer. Sure, the native query works fine, but I can't use it, because there is a complex filtering system in querydsl with a set of dynamic parameters, so that I need the JPQL form. Unfortunately the JPQL with your CASE-WHEN-THEN-ELSE cannot be parsed and I get this exception:

Caused by: java.lang.NullPointerException: Cannot invoke "Object.getClass()" because the return value of "antlr.collections.AST.getNextSibling()" is null

psaraj12 Over a year ago

Can you share one sample data with valueX from your JSON column

diziaq · Accepted Answer · 2022-12-04 06:34:16Z

1

+50

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

answered Dec 4, 2022 at 6:34

diziaq

7,92518 gold badges74 silver badges130 bronze badges

2 Comments

mp9500 Over a year ago

Thanks for your answer. That JPQL get parsed to ... WHERE 'valueX' in (SELECT ...) which returns in Oarcle-DB an empty list, because the column-type is VARCHAR2. Oracle doesn't look inside the Array.

diziaq Over a year ago

What exactly you see inside that (SELECT ..)?

Collectives™ on Stack Overflow

Using Oracle SQL-Function JSON_EXISTS in JPQL

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related