Archive

Archive for April, 2015

JSON_TABLE Chaining

April 12, 2015 4 comments

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

 

Read more…

Categories: JSON Tags: , ,