例: TD_JSONSHRED入れ子配列 - 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"
      }
   ]
}');

以下の単一TD_JSONSHRED呼び出しでは、"students"配列、"students"から、"schools"エントリのみを取得します。結果として得られるテーブルの各行には、各studentの"schools"配列全体が含まれています。

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"}]

以下の入れ子のTD_JSONSHRED呼び出しでは、"schools"配列内の個別JSONオブジェクト データを取得できます。内部関数呼び出しは、上記の関数呼び出しと同じです。上記の出力の各行では、schools列に有効なJSONドキュメントが含まれていることに注意してください。このため、その派生テーブルを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

各schoolに関連付けられたstudentの名前を示す列を含めるには、JSONドキュメントの"name"オブジェクトをTD_JSONSHREDへの内部呼び出しに含めます。以下の例では、最も外側のSELECT文での列名を別名にします。

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