この例では、データベース テーブルに挿入されている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