Select json values from array in Oracle 19c table

Question

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?

Sayan Malakshinov · Accepted Answer · 2021-05-04 19:10:44Z

2

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

answered May 4, 2021 at 19:10

Sayan Malakshinov

8,7501 gold badge21 silver badges28 bronze badges

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

Comments

MT0 · Accepted Answer · 2021-05-04 19:12:11Z

0

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

answered May 4, 2021 at 19:12

MT0

173k12 gold badges70 silver badges136 bronze badges

Comments

Toru · Accepted Answer · 2024-05-16 09:13:55Z

0

Simplified:

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

answered May 16, 2024 at 9:13

Toru

9252 gold badges9 silver badges29 bronze badges

Collectives™ on Stack Overflow

Select json values from array in Oracle 19c table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related