This example shreds data from four small JSON documents that have been inserted into a database table. The data that is in nested JSON object or arrays is extracted using nested calls to TD_JSONSHRED. The JSON documents are stored as VARCHAR data, but the extracted price data is converted to a decimal type.
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}]}}
Now use TD_JSONSHRED to extract the name and price data into a derived database table:
SELECT * FROM TD_JSONSHRED(
ON (SELECT ID, j FROM ShredObjectArray)
USING
ROWEXPR('items')
COLEXPR('name', 'prices')
RETURNTYPES('VARCHAR(10)', 'VARCHAR(80)')
) t;
In the returned result set below, notice the ID column values identify the row of the input table (and therefore the specific JSON document) from which the data was shredded.
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}]
To shred the price data, we need to further shred the JSON objects in the price column. Because some of the price data is in arrays, we cannot use dot notation to specify the data we want. Instead, we can access the nested array and nested object data using nested calls to 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