TD_JSONSHRED Example | Nested Arrays | Teradata Vantage - Example: TD_JSONSHRED Nested Arrays - Advanced SQL Engine - Teradata Database

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