Example: TD_JSONSHRED Multiple Potential Row Expressions - Advanced SQL Engine - Teradata Database

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 will 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, note that the same id 1 is intentionally used here to illustrate 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.

  • It does not require in-depth knowledge of JSONPath.
  • The input data does not have a size restriction. However, it 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

Notice that 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.