sql - Oracle Select From JSON Array

Question

I have a table for some 'settings' and in that table I have a record with a json array. It is a simple array, like this:

"['scenario1','scenario2','scenario3']"

I want to use a sub-select statement in a view to pull this information out so I can use it like this:

select * from table where field_scenario in (select ????? from settings_table where this=that)

I have been looking through documentation and googling for this but for the life of me I can't figure out how to 'pivot' the returning array into individual elements in order to use it.

Oracle 12c I believe, thanks in advance.

question from:https://stackoverflow.com/questions/65875626/oracle-select-from-json-array

Answer 1

Do NOT use regular expression to parse JSON. Use a proper JSON parser:

select *
from   table_name
where  field_scenario in (
  SELECT j.value
  FROM   settings_table s
         OUTER APPLY (
           SELECT value
           FROM   JSON_TABLE(
             s.json,
             '$[*]'
             COLUMNS(
               value VARCHAR2(50) PATH '$'
             )
           )
         ) j
)

Which, for the sample data:

CREATE TABLE settings_table ( json CLOB CHECK ( json IS JSON ) );
INSERT INTO settings_table ( json ) VALUES ( '["scenario1","scenario2","scenario3"]');
INSERT INTO settings_table ( json ) VALUES ( '["scenario5"]');
INSERT INTO settings_table ( json ) VALUES ( '["scenario "quoted""]');
INSERT INTO settings_table ( json ) VALUES ( '["scenario2,scenario4"]');

CREATE TABLE table_name ( id, field_scenario ) AS
SELECT LEVEL, 'scenario'||LEVEL FROM DUAL CONNECT BY LEVEL <= 6 UNION ALL
SELECT 7, 'scenario "quoted"' FROM DUAL;

Outputs:

ID | FIELD_SCENARIO   
-: | :----------------
 1 | scenario1        
 2 | scenario2        
 3 | scenario3        
 5 | scenario5        
 7 | scenario "quoted"

db<>fiddle here