1

My version: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

CREATE TABLE DUMMY1 (id  int NOT NULL PRIMARY KEY, doc1 VARCHAR2 (200) , doc2 VARCHAR2 (200), CONSTRAINT dummy_json1 CHECK (doc1 IS JSON) , CONSTRAINT dummy_json2 CHECK (doc2 IS JSON));

INSERT INTO DUMMY1 VALUES (1, '["12345", "23456", "34567"]', '["ABCD", "EFGH"]’);


select ID, t1.*, t2.* from DUMMY1 d, 
json_table(d.doc1, '$[*]' 
 columns (x VARCHAR2(10) PATH '$')) t1, json_table(d.doc2, '$[*]' 
 columns (x VARCHAR2(10) PATH '$')) t2 WHERE t1.x = ‘12345' AND t2.x='EFGH’;

Returns one record

select ID, t1.*, t2.* from DUMMY1 d, json_table(d.doc1, '$[*]' 
columns (x VARCHAR2(10) PATH '$')) t1, json_table(d.doc2, '$[*]' 
columns (x VARCHAR2(10) PATH '$')) t2 WHERE t1.x = '23456' AND t2.x='EFGH’;

Returns no row.

select ID, t1.*, t2.* from DUMMY1 d, json_table(d.doc1, '$[*]' 
columns (x VARCHAR2(10) PATH '$')) t1, json_table(d.doc2, '$[*]' 
columns (x VARCHAR2(10) PATH '$')) t2;

Returns:

    ID X          X
---------- ---------- ----------
     1 12345      ABCD
     1 12345      EFGH
     1 23456      ABCD
     1 23456      EFGH
     1 34567      ABCD
     1 34567      EFGH

Did I miss anything?

What I was trying to do is that I have a table where 2 of its columns are JSON array, I want to return a result where the 2 given values exist in the JSON array, is there any better way to do this? (since the way I am doing is cross join)

Jidong Xin
  • 119
  • 1
  • 6

1 Answers1

0

I would avoid the cross-product and use something like the following...

SELECT id
FROM   dummy1
WHERE  json_exists (
          doc1,
          '$[*]?(@ == "23456")')
AND    json_exists (
          doc2,
          '$[*]?(@ == "EFGH")');
Padders
  • 276
  • 2
  • 5
  • Thanks, @Padders, that sounds like a better way! However I am running into JSON PATH error with the query you provided, I'm not familiar with json path, can someone shed some lighgt on the correct syntax of json path? – Jidong Xin Oct 17 '18 at 17:06
  • just realized that Oracle 12.1 doesn't support the json-path syntax '$[*]?(@ == "23456")', it was support after 12.2. :( – Jidong Xin Oct 17 '18 at 22:00
  • It is working for me in Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production, is this possibly related to Standard Edition? – Padders Oct 18 '18 at 08:55