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.

"['scenario1','scenario2','scenario3']" is not a JSON array; it is a JSON string. ['scenario1','scenario2','scenario3'] (without the surrounding quotes) would be a JSON array (and to strictly meet the syntax it should be double quotes but most parsers are permissive and allow both single- and double-quotes) . — MT0
– MT0, Commented Jan 24, 2021 at 20:50

MT0 · Accepted Answer · 2021-01-24 20:46:20Z

2

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

edited Jan 24, 2021 at 20:46

answered Jan 24, 2021 at 20:40

MT0

173k12 gold badges70 silver badges136 bronze badges

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

2 Comments

Andy Over a year ago

Thanks so much, this worked a treat. Really appreciate it. I didn't anticipate pulling that information back out would require such a complex query, but I'm very new to json.

Andy Over a year ago

OK I hate to come back here, but this worked well in my initial simple test. However when I baked it into the view, I am getting an error "no more data to read from socket". I took those lines out and put the hard coded data back in and it worked just fine. Any ideas?

Collectives™ on Stack Overflow

Oracle Select From JSON Array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related