This example creates a small JSON document with nested arrays, and inserts it into a database table as a Vantage JSON data type. First we use TD_JSONSHRED to extract data from the outer array, then we use nested TD_JSONSHRED calls to extract data from the inner arrays. Finally, we'll show how you can extract data from different levels of arrays.
create table JsonNestArrays (id integer, jsondoc JSON); insert JsonNestArrays (1,'{ "students":[ { "name":"Cameron", "age":24, "schools":[ { "sname":"Lake", "stype":"elementary" }, { "sname":"Madison", "stype":"middle" }, { "sname":"Rancho", "stype":"high" }, { "sname":"UCI", "stype":"college" } ], "job":"programmer" }, { "name":"Fred", "age":42, "schools":[ { "sname":"Sloat", "stype":"elementary" }, { "sname":"Aptos", "stype":"middle" }, { "sname":"Lowell", "stype":"high" }, { "sname":"UCB", "stype":"college" } ], "job":"manager" } ] }');
The following single TD_JSONSHRED call retrieves only the "schools" entries from the "students" array, "students". Each row of the resulting table contains the entire "schools" array for each student.
SELECT * FROM TD_JSONSHRED( ON (sel id, jsondoc from JsonNestArrays) USING ROWEXPR('students') COLEXPR('schools') RETURNTYPES('VARCHAR(150)') ) AS d1;
id schools -- -------------------------------------------------------------------------------------- 1 [{"sname":"Lake","stype":"elementary"},{"sname":"Madison","stype":"middle"}, {"sname":"Rancho","stype":"high"},{"sname":"UCI","stype":"college"}] 1 [{"sname":"Sloat","stype":"elementary"},{"sname":"Aptos","stype":"middle"}, {"sname":"Lowell","stype":"high"},{"sname":"UCB","stype":"college"}]
The following nested calls to TD_JSONSHRED allow you to get to the individual JSON object data within the "schools" arrays. The inner function call is identical to the one above. Notice that, for each row in the output above, the schools column contains a valid JSON document. So you can use that derived table as input to another call to TD_JSONSHRED.
SELECT * FROM TD_JSONSHRED ( ON ( SELECT * FROM TD_JSONSHRED ( ON (SELECT id, jsondoc FROM JsonNestArrays) USING ROWEXPR('students') COLEXPR('schools') RETURNTYPES('VARCHAR(150)') ) AS d1 ) USING ROWEXPR('') COLEXPR('sname', 'stype') RETURNTYPES('VARCHAR(25)', 'VARCHAR(25)') ) AS d2;
id sname stype ----------- ------------------------- ------------------------- 1 Lake elementary 1 Madison middle 1 Rancho high 1 UCI college 1 Sloat elementary 1 Aptos middle 1 Lowell high 1 UCB college
To include a column showing the student name associated with each school, include the "name" objects from the JSON document in the inner call to TD_JSONSHRED. This example also aliases the column names in the outermost SELECT statement.
SELECT name as student, stype as level, sname as school FROM TD_JSONSHRED ( ON ( SELECT * FROM TD_JSONSHRED ( ON (SELECT id, jsondoc FROM JsonNestArrays) USING ROWEXPR('students') COLEXPR('name','schools') RETURNTYPES('VARCHAR(15)','VARCHAR(150)') ) AS d1 ) USING ROWEXPR('') COLEXPR('sname', 'stype') RETURNTYPES('VARCHAR(25)', 'VARCHAR(25)') ) AS d2 ;
student level school --------------- ------------------------- ------------------------- Cameron elementary Lake Cameron middle Madison Cameron high Rancho Cameron college UCI Fred elementary Sloat Fred middle Aptos Fred high Lowell Fred college UCB