Query json array column with no name in Oracle-CodePudding

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

CodePudding user response：

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"]

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