By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
4 rows affected
SELECT *
FROM TEST_JSON2
WHERE JSON_EXISTS(json_data, '$?(@[*] == "test1" && @[*] == "test2")')
| JSON_DATA |
|---|
| ["test1", "test2"] |
SELECT *
FROM TEST_JSON2
WHERE JSON_EXISTS(json_data, '$[*]?(@ == "test1")')
AND JSON_EXISTS(json_data, '$?(@ == "test2")');
| JSON_DATA |
|---|
| ["test1", "test2"] |