例: TD_JSONSHRED入れ子データ - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - JSONデータ型

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
2020年9月
Language
日本語
Last Update
2021-03-30
dita:mapPath
ja-JP/gzn1554761068186.ditamap
dita:ditavalPath
ja-JP/gzn1554761068186.ditaval
dita:id
evi1472243742653
Product Category
Software
Teradata Vantage

この例では、データベース テーブルに挿入されている4つの小さなJSONドキュメントのデータをシュレッドします。入れ子JSONオブジェクトまたは配列にあるデータは、TD_JSONSHREDへの入れ子の呼び出しを使用して抽出されます。JSONドキュメントはVARCHARデータとして保存されますが、抽出された価格データはDECIMAL形式に変換されます。

CREATE TABLE ShredObjectArray (ID INTEGER, j VARCHAR(200));

INSERT INTO ShredObjectArray VALUES (1, '{"items":[{"name":"charger","prices":[{ "date": "2001-01-01","price":12.99},{ "date": "2002-01-01","price":13.99}]},
                                                   {"name":"phone","prices":{ "date": "2001-01-01","price":12.99}}]}');
INSERT INTO ShredObjectArray VALUES (2, '{"items":{"name":"laptop","prices":[{ "date": "2003-01-01","price":12.99},{ "date": "2004-01-01","price":13.99}]}}');
INSERT INTO ShredObjectArray VALUES (3, '{"items":{"name":"cup","prices":[{ "date": "2004-01-01","price":5.99},{"date": "2005-01-01","price":6.99}]}}');
INSERT INTO ShredObjectArray VALUES (4, '{"items":{"name":"coffee","prices":{ "date": "2001-01-01","price":1.99}}}');
SELECT * FROM ShredObjectArray;

         ID  j
-----------  ----------------------------------------------------------------------------------------------------------------------
          3  {"items":{"name":"cup","prices":[{ "date": "2004-01-01","price":5.99},{"date": "2005-01-01","price":6.99}]}}
          4  {"items":{"name":"coffee","prices": { "date": "2001-01-01","price":1.99}}}
          1  {"items":[{"name":"charger","prices":[{ "date": "2001-01-01","price":12.99},{ "date": "2002-01-01","price":13.99}]},
                       {"name":"phone","prices":{ "date": "2001-01-01","price":12.99}}]}
          2  {"items":{"name":"laptop","prices":[{ "date": "2003-01-01","price":12.99},{ "date": "2004-01-01","price":13.99}]}}


次にTD_JSONSHREDを使用して、派生データベース テーブルに名前と値のデータを抽出します。

SELECT * FROM TD_JSONSHRED(
            ON (SELECT ID, j FROM ShredObjectArray)
            USING
            ROWEXPR('items')
            COLEXPR('name', 'prices')
            RETURNTYPES('VARCHAR(10)', 'VARCHAR(80)')
            ) t;

以下に示す返される結果セットでは、ID列の値が、シュレッドされたデータがあった入力テーブル(つまり特定のJSONドキュメント)の行を特定することに注意してください。

         ID  name        prices
-----------  ----------  --------------------------------------------------------------------------------
          3  cup         [{"date":"2004-01-01","price":5.99},{"date":"2005-01-01","price":6.99}]
          4  coffee      {"date":"2001-01-01","price":1.99}
          1  charger     [{"date":"2001-01-01","price":12.99},{"date":"2002-01-01","price":13.99}]
          1  phone       {"date":"2001-01-01","price":12.99}
          2  laptop      [{"date":"2003-01-01","price":12.99},{"date":"2004-01-01","price":13.99}]

価格データをシュレッドするには、価格列でJSONオブジェクトをさらにシュレッドする必要があります。一部の価格データは配列内にあるので、ドット表記法を使用して必要なデータを指定することはできません。代わりに、TD_JSONSHREDへの入れ子呼び出しを使用して、入れ子配列と入れ子オブジェクトのデータにアクセスできます。

SELECT * FROM TD_JSONSHRED (
    ON (
        SELECT * FROM TD_JSONSHRED(
            ON (SELECT ID, j FROM ShredObjectArray)
            USING
            ROWEXPR('items')
            COLEXPR('name', 'prices')
            RETURNTYPES('VARCHAR(10)', 'VARCHAR(80)')
            ) t
       )
    USING
    ROWEXPR('')
    COLEXPR('date', 'price')
    RETURNTYPES('VARCHAR(10)', 'DECIMAL(5,2)')
    ) t2
ORDER BY 1, 2, 3, 4;

         ID  name        date          price
-----------  ----------  ----------  -------
          1  charger     2001-01-01    12.99
          1  charger     2002-01-01    13.99
          1  phone       2001-01-01    12.99
          2  laptop      2003-01-01    12.99
          2  laptop      2004-01-01    13.99
          3  cup         2004-01-01     5.99
          3  cup         2005-01-01     6.99
          4  coffee      2001-01-01     1.99