Thanks for the question, Brian.

Asked: February 05, 2018 - 1:09 pm UTC

Last updated: February 07, 2018 - 10:40 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Good morning folks

I'm trying to consume JSON documents that basically follow this structure:

{
"items": [
  {
  "id": "111A",
  "someAttributes": [
    {
    "name": "anAttribute",
    "value": "A Value"
    },
    {
    "name": "aDifferentAttribute",
    "value": "A Different Value"
    }
  ],
  "someOtherAttributes": [
    {
    "name": "anotherAttribute",
    "value": "Another Value"
    },
    {
    "name": "lookAnAttribute",
    "value": "Look, another value!"
    }
  ]
]}

And, I need to get back:

ITEM_ID       ATTR_NAME                 ATTR_VALUE
------------- ------------------------- ------------------------------
111A          anAttribute               A Value
111A          anDifferentAttribute      A Different Value
111A          anotherAttribute          Another Value
111A          lookAnAttribute           Look, another value!

Currently, I'm combining the results of two traversals of these documents, via json_table, like this:

SELECT item_id, attr_name, attr_value
  FROM json_table(doc_json, '$.items[*]' COLUMNS (
    item_id VARCHAR2 PATH '$.id',
    NESTED PATH '$.someAttributes[*]' COLUMNS (
      attr_name VARCHAR2 PATH '$.name',
      attr_value VARCHAR2 PATH '$.value'
    )
   )
  )
UNION ALL
SELECT item_id, attr_name, attr_value
  FROM json_table(doc_json, '$.items[*]' COLUMNS (
    item_id VARCHAR2 PATH '$.id',
    NESTED PATH '$.someOtherAttributes[*]' COLUMNS (
      attr_name VARCHAR2 PATH '$.name',
      attr_value VARCHAR2 PATH '$.value'
    )
   )
  );

It works. But, is Oracle doing two traversals of the JSON in this case? And, if so, is there any way to do the same thing in a single traversal? These documents can be quite large (> 500MB). I've considered using json_table chaining, but the actual contents of the "someAttributes" and "someOtherAttributes" object can be very large, often exceeding the 32KB extended max string size limit. Is my current solution my only option?

Thanks so much
Brian

and Chris said...

Yes. For each union all subquery, you'll (full) scan the table again.

You can chain many nested paths in a single call to json_table. But for each new nesting, you'll get a new set of columns:

create table t (
  doc_json varchar2(1000) check (doc_json is json)
);

insert into t values ('{
"items": [
  {
  "id": "111A",
  "someAttributes": [
    {
    "name": "anAttribute",
    "value": "A Value"
    },
    {
    "name": "aDifferentAttribute",
    "value": "A Different Value"
    }
  ],
  "someOtherAttributes": [
    {
    "name": "anotherAttribute",
    "value": "Another Value"
    },
    {
    "name": "lookAnAttribute",
    "value": "Look, another value!"
    }
  ]
  }
]}');
commit;

with rws as (
SELECT j.*
  FROM t, json_table(doc_json, '$.items[*]' COLUMNS (
    item_id VARCHAR2 PATH '$.id',
    NESTED PATH '$.someAttributes[*]' COLUMNS (
      attr_name1 VARCHAR2 PATH '$.name',
      attr_value1 VARCHAR2 PATH '$.value'
    ),
    NESTED PATH '$.someOtherAttributes[*]' COLUMNS (
      attr_name2 VARCHAR2 PATH '$.name',
      attr_value2 VARCHAR2 PATH '$.value'
    )
   )
  ) j
)
  select *
  from   rws;

ITEM_ID   ATTR_NAME1            ATTR_VALUE1         ATTR_NAME2         ATTR_VALUE2            
111A      anAttribute           A Value             <null>             <null>                 
111A      aDifferentAttribute   A Different Value   <null>             <null>                 
111A      <null>                <null>              anotherAttribute   Another Value          
111A      <null>                <null>              lookAnAttribute    Look, another value!

Which is a problem if you want all the values to appear under one set of name/value columns.

Luckily this is easy to fix with a bit of unpivoting:

with rws as (
SELECT j.*
  FROM t, json_table(doc_json, '$.items[*]' COLUMNS (
    item_id VARCHAR2 PATH '$.id',
    NESTED PATH '$.someAttributes[*]' COLUMNS (
      attr_name1 VARCHAR2 PATH '$.name',
      attr_value1 VARCHAR2 PATH '$.value'
    ),
    NESTED PATH '$.someOtherAttributes[*]' COLUMNS (
      attr_name2 VARCHAR2 PATH '$.name',
      attr_value2 VARCHAR2 PATH '$.value'
    )
   )
  ) j
)
  select item_id, attr_name, attr_value
  from   rws
  unpivot ( (attr_name, attr_value) for src in ( 
      (attr_name1, attr_value1), 
      (attr_name2, attr_value2)
    )
  );

ITEM_ID   ATTR_NAME             ATTR_VALUE             
111A      anAttribute           A Value                
111A      aDifferentAttribute   A Different Value      
111A      anotherAttribute      Another Value          
111A      lookAnAttribute       Look, another value!

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Possible alternative for some cases

Kim Berg Hansen, February 05, 2018 - 8:30 pm UTC

If the items array does not contain other arrays of name/value pairs than the two arrays of interest, an alternative can be to just wildcard the arrayname instead of someAttributes and someOtherAttributes:

SELECT j.*
  FROM t, json_table(doc_json, '$.items[*]' COLUMNS (
    item_id VARCHAR2 PATH '$.id',
    NESTED PATH '$.*[*]' COLUMNS (
      attr_name VARCHAR2 PATH '$.name',
      attr_value VARCHAR2 PATH '$.value'
    )
   )
  ) j
;

The disadvantage of course is that it will pick up all name/value arrays within the items array. So you need to be sure of your JSON to use the wildcard approach.

February 06, 2018 - 9:53 am UTC

Thanks Kim, didn't know you could do that! :)

using JSON EXISTS

Rajeshwaran, Jeyabal, February 06, 2018 - 10:29 am UTC

Team,

Another approach using JSON_EXISTS option.

demo@ORA12C> select t1.*
  2  from t , json_table( x, '$.items[*]' columns(
  3      item_id varchar2 path '$.id',
  4      nested path '$.someAttributes[*]'
  5        columns (
  6          name1 varchar2 path '$.name' ,
  7          value1 varchar2 path '$.value',
  8          exists1 number exists path '$.name'
  9        ) ,
 10      nested path '$.someOtherAttributes[*]'
 11        columns(
 12          name2 varchar2 path '$.name' ,
 13          value2 varchar2 path '$.value',
 14          exists2 number exists path '$.name'
 15        )
 16      ) ) t1
 17  /

ITEM_ID    NAME1                  VALUE1                     EXISTS1 NAME2                  VALUE2             EXISTS2
---------- ---------------------- ----------------------- ---------- ---------------------- ----------------------- ----------
111A       anAttribute            A Value                          1
111A       aDifferentAttribute    A Different Value                1
111A                                                                 anotherAttribute       Another Value                    1
111A                                                                 lookAnAttribute        Look, another value!             1

demo@ORA12C> select item_id,
  2      case when exists1 ='1' then name1
  3           when exists2 ='1' then name2 end as new_name ,
  4      case when exists1 ='1' then value1
  5           when exists2 ='1' then value2 end as new_value
  6  from (
  7  select t1.*
  8  from t , json_table( x, '$.items[*]' columns(
  9      item_id varchar2 path '$.id',
 10      nested path '$.someAttributes[*]'
 11        columns (
 12          name1 varchar2 path '$.name' ,
 13          value1 varchar2 path '$.value',
 14          exists1 number exists path '$.name'
 15        ) ,
 16      nested path '$.someOtherAttributes[*]'
 17        columns(
 18          name2 varchar2 path '$.name' ,
 19          value2 varchar2 path '$.value',
 20          exists2 number exists path '$.name'
 21        )
 22      ) ) t1
 23      )
 24  /

ITEM_ID    NEW_NAME                  NEW_VALUE
---------- ------------------------- -------------------------
111A       anAttribute               A Value
111A       aDifferentAttribute       A Different Value
111A       anotherAttribute          Another Value
111A       lookAnAttribute           Look, another value!

demo@ORA12C>

February 06, 2018 - 4:22 pm UTC

Yep, you could do that too.

Brian Ballard, February 06, 2018 - 6:44 pm UTC

Ohhhhh these are some excellent suggestions. I tried the wildcarding approach that was mentioned at some point, as well. It's a good suggestion, but sadly the actual production JSON produced has a ridiculous number of objects that also get picked up by the object name wildcard.

I never consider the multiple "NESTED PATH" + unpivot approach, though. I think that will fit the bill perfectly. Thanks everyone!!

Brian

February 07, 2018 - 10:40 am UTC

Great, glad we could help :)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Possible alternative for some cases

using JSON EXISTS

More to Explore

SQL

Avoiding multiple json traversals

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Possible alternative for some cases

using JSON EXISTS

More to Explore

SQL