This example shreds data from two small JSON documents that have been inserted into a database table, then displays the extracted data in columns of the derived table that is returned by the function. The JSON documents are stored as VARCHAR data, but one column of the extracted data is returned as DECIMAL data.
CREATE TABLE ShreddedData (ID INTEGER, j VARCHAR(80)); INSERT INTO ShreddedData VALUES (1, '{"items":[{"name":"charger","price":12.99},{"name":"phone","price":599.99}]}'); INSERT INTO ShreddedData VALUES (2, '{"items":{"name":"laptop","price":999.99}}');
SELECT * FROM ShreddedData; ID j ----------- -------------------------------------------------------------------------------- 1 {"items":[{"name":"charger","price":12.99},{"name":"phone","price":599.99}]} 2 {"items":{"name":"laptop","price":999.99}}
Now use TD_JSONSHRED to extract the names and values from the JSON data into a derived database table:
SELECT * FROM TD_JSONSHRED( ON (SELECT ID, j FROM ShreddedData) USING ROWEXPR('items') COLEXPR('name', 'price') RETURNTYPES('VARCHAR(20)', 'DECIMAL(10,2)') ) 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 price ----------- -------------------- ------------ 1 charger 12.99 1 phone 599.99 2 laptop 999.99