Parsing JSON Array without keys in NESTED PATH-CodePudding

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" ]}] }');

CodePudding user response：

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;

EDIT:

To get values both from object members and from scalar members of the nested array, you can do something like this. Use nvl(k_token, token) if you just need the value and don't need to know if it comes from an array of objects or an array of scalars. Note that this solution will work even if you have objects and scalars mixed together in the same JSON (in the same nested array).

select p.id, j.k_token, j.token
from   parse_json_array p,
       json_table(data, '$.dt[*].values[*]'
           columns( k_token path '$.key',
                      token path '$'
                  )
       ) j
;