Query json array column with no name in Oracle

Question

I am using Oracle19c and I have the following table storing json arrays like below:

CREATE TABLE "TEST_JSON2" 
("JSON_DATA") as
select '["test1"]' from dual union all
select '["test1", "test2"]' from dual union all
select '["test2"]' from dual union all
select '["test3"]' from dual;

I want to make a query to retrieve rows that contains both the "test1" and the "test2" elements in the array.

So far I have achieved it with the following query:

SELECT * 
FROM TEST_JSON2
WHERE
JSON_EXISTS(json_data, '$?(@ == "test1")') AND
JSON_EXISTS(json_data, '$?(@ == "test2")');

I am wondering if the above can be optimised more - for example I was thinking something like below but it is not pick the row with the array that contains both elements:

   SELECT * 
   FROM TEST_JSON2
   WHERE 
   JSON_EXISTS(json_data, '$?(@ == "test1" && @ == "test2")');

MT0 · Accepted Answer · 2022-02-24 10:08:33Z

2

You can [*] to explicitly reference the array elements and let @ reference the containing array:

SELECT * 
FROM   TEST_JSON2
WHERE  JSON_EXISTS(json_data, '$?(@[*] == "test1" && @[*] == "test2")')

Which, for the sample data, outputs:

JSON_DATA

["test1", "test2"]

(Note: your final query was implicitly being converted to JSON_EXISTS(json_data, '$[*]?(@ == "test1" && @ == "test2")') which will not work as there can never be a single array element that is simultaneously equal to both "test1" and "test2".)

db<>fiddle here

answered Feb 24, 2022 at 10:08

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

Query json array column with no name in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related