TD_JSONSHRED Table Operator Examples | Teradata Vantage - Example: TD_JSONSHRED - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
gzn1554761068186.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
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 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