Example: TD_JSONSHRED - Teradata Vantage

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 following returned result set, 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