Home > JSON > JSON_TABLE Chaining

JSON_TABLE Chaining

Here’s the first article of the JSON category, focusing on JSON_TABLE for unnesting nested JSON arrays.

 

0- Setup

We’ll first create a table to hold some JSON documents :

create table tmp_json (
  id integer
, doc clob check (doc is json)
)
lob (doc) store as securefile (cache);

and some docs :

insert into tmp_json
values (1,
'{"id":1,
 "myArray":[
  {"attr1":1, "attr2":"string1", "attr3":[1,2,3]},
  {"attr1":2, "attr2":"string2", "attr3":null},
  {"attr1":3, "attr2":"string3", "attr3":[4,5]}
 ]}'
);

insert into tmp_json
values (2,
'{"id":2,
 "myArray":[
  {"attr1":1, "attr2":"string1", "attr3":[7,8]},
  {"attr1":2, "attr2":"string2", "attr3":null}
 ]}'
);

 

1- “Naive” unnesting method

Similarly to what we can do with XMLTable chaining, it’s natural to try something like this :

select jt1.id, jt2.*
from tmp_json t
   , json_table(t.doc,
       '$'
       columns id        number path '$.id'
             , my_array  varchar2(4000) format json path '$.myArray'
     ) jt1
   , json_table(jt1.my_array,
       '$[*]'
       columns attr1 number path '$.attr1'     
     ) jt2
;

The first JSON_TABLE (jt1) extracts the id property then passes the array to a second JSON_TABLE (jt2) to extract each item in its own relational row.
However, that query does not work and returns :

ORA-40556: unsupported chaining of JSON_TABLE

For some reasons, that kind of direct chaining is not supported.
So, what’s the official, supported way of chaining JSON_TABLE calls?

 

2- JSON_TABLE NESTED PATH clause

The NESTED PATH clause has been designed to handle the situation :

SQL> select jt1.*
  2  from tmp_json t
  3     , json_table(t.doc,
  4         '$'
  5         columns id number path '$.id'
  6               , nested    path '$.myArray[*]'
  7                   columns (
  8                     attr1  number       path '$.attr1'
  9                   , attr2  varchar2(10) path '$.attr2'
 10                   , nested path '$.attr3[*]'
 11                       columns ( attr3_item  number path '$' )
 12                   )
 13       ) jt1
 14  ;
 
        ID      ATTR1 ATTR2      ATTR3_ITEM
---------- ---------- ---------- ----------
         1          1 string1             1
         1          1 string1             2
         1          1 string1             3
         1          2 string2    
         1          3 string3             4
         1          3 string3             5
         2          1 string1             7
         2          1 string1             8
         2          2 string2    

Nested sets are joined in an outer join fashion so that an empty or null array does not eliminate the parent row.

The main reason for this nested syntax is, I guess, efficiency.
Since JSON functions parse the content every time they’re called, unnesting any number of levels in a single call (vs. using additional JSON_TABLEs) is obviously better.

Here’s the explain plan of the above query :

Execution Plan
----------------------------------------------------------
Plan hash value: 1256636746

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          | 16336 |  6365K|    59   (0)| 00:00:01 |
|   1 |  NESTED LOOPS          |          | 16336 |  6365K|    59   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL    | TMP_JSON |     2 |   782 |     3   (0)| 00:00:01 |
|   3 |   JSONTABLE EVALUATION |          |       |       |            |          |
-----------------------------------------------------------------------------------

Only one JSONTABLE EVALUATION is used.

We’ll now see how to chain two JSON_TABLEs without the NESTED clause, effectively by-passing the ORA-40556 error, and what practical use the method has over the NESTED syntax.

 

3- Workaround to the ORA-40556 error

The key is to make the first JSON_TABLE a non-mergeable view and join the 2nd JSON_TABLE by passing the generated column from the 1st, mimicking what we would do with XMLTABLE and an XMLType fragment.

This is the first query rewritten with that in mind :

SQL> with tmp as (
  2    select /*+ no_merge */ jt1.*
  3    from tmp_json t
  4       , json_table(t.doc,
  5           '$'
  6           columns id        number path '$.id'
  7                 , my_array  varchar2(4000) format json path '$.myArray'
  8         ) jt1
  9  )
 10  select t.id
 11       , jt2.attr1
 12  from tmp t
 13     , json_table(t.my_array,
 14         '$[*]'
 15         columns attr1 number path '$.attr1'
 16       ) jt2
 17  ;
 
        ID      ATTR1
---------- ----------
         1          1
         1          2
         1          3
         2          1
         2          2

It works, but as expected the explain plan now shows two JSONTABLE EVALUATION, hence two parse operations :

Execution Plan
----------------------------------------------------------
Plan hash value: 4133733999

-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |   133M|   311G|   443K  (1)| 00:00:18 |
|   1 |  NESTED LOOPS            |          |   133M|   311G|   443K  (1)| 00:00:18 |
|   2 |   VIEW                   |          | 16336 |    39M|    59   (0)| 00:00:01 |
|   3 |    NESTED LOOPS          |          | 16336 |  6301K|    59   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL    | TMP_JSON |     2 |   782 |     3   (0)| 00:00:01 |
|   5 |     JSONTABLE EVALUATION |          |       |       |            |          |
|   6 |   JSONTABLE EVALUATION   |          |       |       |            |          |
-------------------------------------------------------------------------------------

Of course, there’s no value added in using this approach over the NESTED clause when all data is JSON.
However, it may come in handy when we want to unnest embedded JSON data in escaped form, e.g.

insert into tmp_json
values (3,
'{"id":3,
 "myArray":[
  {"attr1":1, "attr2":"{\"id\":\"item1\",\"data\":1.5}"},
  {"attr1":2, "attr2":"{\"id\":\"item2\",\"data\":2.3}"}
 ]}'
);

Here, myArray[*].attr2 is a string representing a JSON object, so we first have to extract it as a VARCHAR2 column and pass it to the 2nd JSON_TABLE for parsing :

SQL> with tmp as (
  2    select /*+ no_merge */ jt1.*
  3    from tmp_json t
  4       , json_table(t.doc, '$'
  5           columns doc_id  number path '$.id'
  6                 , nested path '$.myArray[*]'
  7                     columns (
  8                       attr1 number         path '$.attr1'
  9                     , attr2 varchar2(4000) path '$.attr2'
 10                     )
 11         ) jt1
 12    where t.id = 3
 13  )
 14  select t.doc_id
 15       , t.attr1
 16       , jt2.*
 17  from tmp t
 18     , json_table(t.attr2, '$'
 19         columns attr1 varchar2(5) path '$.id'
 20               , data  number      path '$.data'
 21       ) jt2
 22  ;
 
    DOC_ID      ATTR1 ATTR1       DATA
---------- ---------- ----- ----------
         3          1 item1        1.5
         3          2 item2        2.3

Advertisements
Categories: JSON Tags: , ,
  1. JD
    May 3, 2015 at 00:26

    Thank you for an excellent blog! This is exactly what I’m trying to do: “unnest embedded JSON data in escaped form” as you say.

    Here’s my SQL (almost exactly yours)

    with tmp as (
    SELECT d.*
    FROM ulocations ul,
    json_table(ul.upjson, ‘$’
    columns(
    NESTED PATH ‘$.values[*].nameValuePairs’
    COLUMNS (

    updated VARCHAR2(19 CHAR) PATH ‘$.updated’
    , id varchar2(9 char) path ‘$._id’
    , upJSON VARCHAR2(2000 CHAR) PATH ‘$.upJSON’
    )) ) d
    where d.id = 0
    )
    select t.updated
    , t.id
    , jt2.*
    from tmp t
    , json_table(t.upJSON, ‘$’
    columns mProvider varchar2(5) path ‘$.mProvider’
    , mLongitude number path ‘$.mLongitude’
    ) jt2
    ;

    Here’s what I get :-(
    ORA-40556: unsupported chaining of JSON_TABLE

  2. JD
    May 3, 2015 at 00:30

    oops. I forgot the most necessary:
    /*+ no_merge */

    THANK YOU THANK YOU THANK YOU.

  3. JD
  1. January 13, 2016 at 13:03

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: