Usage Notes - 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ā„¢
  • TD_JSONSHRED supports input CLOB data type sizes up to 2 GB.
  • Although TD_JSONSHRED does not support extracting data directly from nested arrays in a JSON document, you can nest calls to TD_JSONSHRED to shred data from nested arrays. For example:
    SELECT * from TD_JSONSHRED
    (ON
      (SELECT * FROM TD_JSONSHRED
        (ON 
          (SELECT ID, jsondoc FROM jsontable)
            USING
              ROWEXPR('employees')
              COLEXPR('address')
              RETURNTYPES('VARCHAR(100)')
         ) AS d1
      )
      USING
        ROWEXPR('')
        COLEXPR('street','city','state','zipcode')
        RETURNTYPES('VARCHAR(20)','VARCHAR(20)','VARCHAR(2)','VARCHAR(10)')
    ) AS d2;
    The inner TD_JSONSHRED creates a derived table (d1) having a column that holds the value of the JSON address object. This value is an array that was nested in the "employees" array in the original JSON document. The nested address array itself is a JSON document, so its constituent values can be shredded by the outer TD_JSONSHRED into individual columns in the derived table, d2. See Example: TD_JSONSHRED Nested Arrays.