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