Parsing JSON Array without keys in NESTED PATH

Question

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

Agree with mathguy, providing the expected result would make the question clearer. Having said that it appears you probably just want PATH '$'. — Padders
– Padders, Commented Dec 2, 2021 at 18:38

score 1 · Accepted Answer · 2021-12-03 02:06:20Z

1

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
;

edited Dec 3, 2021 at 2:06

answered Dec 2, 2021 at 18:43

user5683823

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

4 Comments

Marmite Bomber Over a year ago

yep, '$' works, thanks, it seems to by something like xpath text(). "doesn't make a lot of sense" - it seems you work only in lucky projects

user5683823 Over a year ago

@MarmiteBomber - I don't work at all anymore (and never worked in IT in any capacity anyway); I understand what you mean, but "having to work on a project you can't control" doesn't mean it makes sense!

Marmite Bomber Over a year ago

Understood. Despite the sense is a selection of the array value posible with one path in both cases with and without keys? @padders pls comment as well.

user5683823 Over a year ago

@MarmiteBomber - you can combine the two queries in one (two different columns in the columns clause - or combine them in one using nvl). See EDIT to my reply. Not pretty, but it will work. The more curious thing is that the value of dt is an array - in your example it contains a single object with key values and value a (nested) array - either of objects or of scalars. In real life, does the outer array (the value of dt) have other members, in addition to the object with key values? And if so, what do you need to extract from it?

Collectives™ on Stack Overflow

Parsing JSON Array without keys in NESTED PATH

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related