This example creates a small JSON document with nested objects, inserts the document into a database table as a CLOB data type, then uses TD_JSONSHRED and simplified dot notation to extract the data from the unnested and nested objects. The JSON document is created as a CLOB object, but the extracted data is returned as VARCHAR data.
CREATE TABLE jsonTableClob (id INTEGER, j CLOB);
INSERT jsonTableClob (1,
'{ "items" : [ { "a" : 1,
"b" : [ 1, "a", 2 ],
"c" : { "c-d" : [],
"c-e" : {},
"c-f" : 123
}
},
{ "a" : 2,
"b" : [ 2, "b", 232 ],
"c" : { "c-d" : [0, 1],
"c-e" : {"c-e-y":"z"},
"c-f" : 234
}
},
{}
]
}' );
SELECT * FROM TD_JSONSHRED(
ON (SELECT id, j FROM jsonTableClob)
USING
ROWEXPR('items')
COLEXPR('a','b','c','c.c-d','c.c-e','c.c-e.c-e-y','c.c-f')
RETURNTYPES('VARCHAR(5)','VARCHAR(15)','VARCHAR(45)','VARCHAR(10)',
'VARCHAR(15)','VARCHAR(5)','VARCHAR(5)')
) dt;
In the following returned result set, the data extracted and displayed as column c contains all the objects that are nested within JSON object "items" : "c". Those nested objects are further broken out and displayed individually in columns c-d, c-e, c-e-y, and c-f by using dot notation (c.c-d, c.c-e, c.c-e-y, and c.c-f) in the COLEXPR to identify the nested objects. The final JSON object in the document is empty, so the last row in the derived table does not include values for the JSON data columns.
Although the original JSON document was stored as a CLOB, the data is shredded to VARCHAR columns.
id a b c c-d c-e c-e-y c-f
----------- ----- --------------- --------------------------------------------- ---------- --------------- ----- -----
1 1 [1,"a",2] {"c-d":[],"c-e":{},"c-f":123} [] {} ? 123
1 2 [2,"b",232] {"c-d":[0,1],"c-e":{"c-e-y":"z"},"c-f":234} [0,1] {"c-e-y":"z"} z 234
1 ? ? ? ? ? ? ?