Parsing JSON Array without keys in NESTED PATH

December 2, 2021

I have a JSON like this (see the test setup below)

{
  "dt" :
  [
    {
      "values" :
      [
        {
          "key" : "a"
        },
        {
          "key" : "b"
        }
      ]
    }
  ]
}

and it is straightforeward to parse the inner array as it has keys as follows

SELECT tab.id, 
       jt.*
FROM parse_json_array tab,
       json_table(data, '$.dt[*]'
         COLUMNS (NESTED PATH '$.values[*]' COLUMNS(
                     key     PATH '$.key'  ) 
    )) AS "JT"
where tab.id = 1;

which returns

ID, KEY
--------
1   a
1   b

But if the inner array has no keys, how could I addapt the path in NESTED PATH?

{
  "dt" :
  [
    {
      "values" :
      [
        "a",
        "b"
      ]
    }
  ]
}

All my try such as key PATH '$.*' or key PATH '*' return null or syntax error.

Note I do not need a solution, that parse both variants, but it would be of course a bonus😉

I’m on XE 18.4.0.0.0

Test data

create table parse_json_array
(id int primary key,
 data CLOB constraint c1 check(data is JSON)
 );

insert into parse_json_array (id, data) values (1, '{ "dt" : [ {"values" : [{"key" : "a"} , {"key" : "b" } ]} ] }'); 
insert into parse_json_array (id, data) values (2, '{ "dt" : [ {"values" : [  "a" ,   "b" ]}] }');

>Solution :

This will give you the id and the values within the nested array, when it’s just an array of scalars rather than objects.

SELECT tab.id, 
       jt.*
FROM parse_json_array tab,
       json_table(data, '$.dt[*].values[*]'
         COLUMNS  key     PATH '$'  ) 
     AS "JT"
where tab.id = 2;

Storing JSON in both formats, and even more so, asking for a solution that works for both, doesn’t make a lot of sense; the JSON structure is different. It’s like asking for a SQL SELECT query that works for two different tables with different column sets.

If you need a solution with nested path (perhaps because you must pick out additional bits of data, which you did not share with us), you can do something like this (which is what Padders suggested in a comment):

SELECT tab.id, 
       jt.*
FROM parse_json_array tab,
       json_table(data, '$.dt[*]' columns(
         nested path '$.values[*]'
         COLUMNS  (key     PATH '$'  )) )
     AS "JT"
where tab.id = 2;