Example: TD_JSONSHRED Common Value - Advanced SQL Engine - Teradata Database

This example demonstrates how to shred a common value processed once.

CREATE MULTISET TABLE jsonTable
(
    id      INTEGER,
    data    JSON
);

Populate the table with sample JSON data that will be shredded. Extract a and b under rowData as the common data globalId.

INSERT INTO jsonTable VALUES (1, '{
    "globalId" : 1234,
    "rowData": [
        { "a" : 1, "b" : "a" },
        { "a" : 2, "b" : "b" },
        { "a" : 3, "b" : "c" }
    ]
}');

• Method 1: Extract Early

  This method combines dot notation (JsonExtractValue) with TD_JSONSHRED. It processes the data only once.

  SELECT globalId, a, b
  FROM TD_JSONSHRED (ON
      (SELECT id, CAST(data.globalId AS INTEGER), data FROM jsonTable)
      USING
      ROWEXPR('rowData')
      COLEXPR('a', 'b')
      RETURNTYPES ('INTEGER', 'VARCHAR(10)')
  ) d(id, globalId, a, b)
  ;

  Output:

     globalId            a  b
  -----------  -----------  ----------
         1234            1  a
         1234            2  b
         1234            3  c

• Method 2: Extract Late

  This approach should be avoided because it requires repeated processing of the same data. It causes performance issues.

  SELECT CAST(data.globalId AS INTEGER) as globalId, a, b
  FROM TD_JSONSHRED (ON
      (SELECT id, data as d, data FROM jsonTable)
      USING
      ROWEXPR('rowData')
      COLEXPR('a', 'b')
      RETURNTYPES ('INTEGER', 'VARCHAR(10)')
  ) d(id, data, a, b)
  ;