CODEWITHSUNDEEP
arraysjsonoracle-database
NewRodeoJson
NewRodeoJson

Reputation: 3

Select json values from array in Oracle 19c table

Newer to working with JSON and newly upgraded Oracle 19c DB. I'm receiving a JSON array back from an api and storing it in an Oracle 19c table column with IS JSON (STRICT) constraint.

[ {"key1":"valueA", "key2":"valueB"}, {"key1":"valueC", "key2":"valueD"} ]

I need to select values in column form:

KEY1    KEY2
valueA  valueB
valueC  valueD

This returns one row with null columns.

Select jt.* 
  From json_data, 
       json_table(myData, '$.[*]' 
       columns( key1, key2)) jt;

I can't seem to make the Oracle functions (json_table, json_query, json_value, ...) handle this without wrapping the array in an object.

{ "base":[ {"key1":"valueA", "key2":"valueB"}, {"key1":"valueC", "key2":"valueD"} ] }

Then this query works:

Select jt.* 
  From json_data, 
       json_table(myData, '$.base[*]' 
       columns( key1, key2)) jt;

Is there a shortcoming with the Oracle functions or what am I doing wrong?

Upvotes: 0

Views: 2891

Answers (3)

Toru
Toru

Reputation: 905

Simplified:

SELECT *
FROM json_table(
    (SELECT '[ {"key1":"valueA", "key2":"valueB"}, {"key1":"valueC", "key2":"valueD"} ]' FROM dual),
'$[*]' COLUMNS(key1, key2));

Upvotes: 0

MT0
MT0

Reputation: 167774

You want $[*] not $.[*]

SELECT jt.* 
FROM   json_data
       CROSS APPLY json_table(
         myData,
         '$[*]' 
         columns(
           key1,
           key2
         )
       ) jt;

Which for the sample data:

CREATE TABLE json_data ( myData VARCHAR2(2000) CHECK( myData IS JSON(STRICT) ) );

INSERT INTO json_data ( myData )
VALUES ( '[ {"key1":"valueA", "key2":"valueB"}, {"key1":"valueC", "key2":"valueD"} ]' );

Outputs:

KEY1 KEY2
valueA valueB
valueC valueD

db<>fiddle here

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Select jt.* 
  From json_data, 
       json_table(myData, '$[*]' 
       columns( key1, key2)) jt;

Full test case with results:

with json_data(myData) as (
select '[ {"key1":"valueA", "key2":"valueB"}, {"key1":"valueC", "key2":"valueD"} ]' from dual
)
Select jt.* 
  From json_data, 
       json_table(myData, '$[*]' 
       columns( key1, key2)) jt;

KEY1                 KEY2
-------------------- --------------------
valueA               valueB
valueC               valueD

Upvotes: 2

Related Questions