Example: TD_JSONSHRED Multiple Potential Row Expressions - Teradata Vantage

These examples demonstrate two approaches that can be used to shred JSON data with multiple potential row expressions.

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

Populate the table with sample JSON data, which is to be shredded. Extract a and b under rowData1 and rowData2.

INSERT INTO jsonTable VALUES (1, '{
    "rowData1": [
        { "a" : 1, "b" : "a" },
        { "a" : 2, "b" : "b" },
        { "a" : 3, "b" : "c" }
    ],
    "rowData2": [
        { "a" : 4, "b" : "d" },
        { "a" : 5, "b" : "e" },
        { "a" : 6, "b" : "f" }
    ]
}');

For the next insertion, the same id 1 is intentionally used here to show the sequence of events.

INSERT INTO jsonTable VALUES (1, '{
    "rowData1": [
        { "a" : 7, "b" : "g" },
        { "a" : 8, "b" : "h" },
        { "a" : 9, "b" : "i" }
    ],
    "rowData2": [
        { "a" : 10, "b" : "j" },
        { "a" : 11, "b" : "k" },
        { "a" : 12, "b" : "l" }
    ]
}');

• Method 1: Use JsonExtract

  Considerations:

  • The input must be JSON or converted to JSON. Therefore, there is a size restriction of 16MB.
  • Relies on JSONPath, which can be complicated.

  SELECT id, data.JsonExtract('$.[rowData1,rowData2][*]')
  FROM jsonTable;
  
  SELECT a, b
  FROM TD_JSONSHRED (ON
      (SELECT id, data.JsonExtract('$.[rowData1,rowData2][*]')
       FROM jsonTable)
      USING
      ROWEXPR('')
      COLEXPR('a', 'b')
      RETURNTYPES ('INTEGER', 'VARCHAR(10)')
  ) d(id, a, b)
  ;

  Output:

            a  b
  -----------  ----------
            1  a
            2  b
            3  c
            4  d
            5  e
            6  f
            7  g
            8  h
            9  i
           10  j
           11  k
           12  l

• Method 2: Use UNION ALL
  This approach is more flexible, for the following reasons:

  • Does not require in-depth knowledge of JSONPath.
  • Input data does not have a size restriction.

  However, this approach takes multiple rounds of shredding the data, which has performance implications.

  SELECT a, b
  FROM TD_JSONSHRED (ON
      (SELECT id, data
       FROM jsonTable)
      USING
      ROWEXPR('rowData1')
      COLEXPR('a', 'b')
      RETURNTYPES ('INTEGER', 'VARCHAR(10)')
  ) d(id, a, b)
  UNION ALL
  SELECT a, b
  FROM TD_JSONSHRED (ON
      (SELECT id, data
       FROM jsonTable)
      USING
      ROWEXPR('rowData2')
      COLEXPR('a', 'b')
      RETURNTYPES ('INTEGER', 'VARCHAR(10)')
  ) d2(id, a, b)
  ;

  Output:

            a  b
  -----------  ----------
            1  a
            2  b
            3  c
            7  g
            8  h
            9  i
            4  d
            5  e
            6  f
           10  j
           11  k
           12  l

The output values sequence are different in each approach. In the first query using JsonExtract, the row data is prepared once. In the second query using UNION ALL, the same data is processed and shredded twice.