Example: JSON_TABLE Using Ordinal Column

Teradata Vantageā„¢ JSON Data Type

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K
The example shows JSON_TABLE using ordinal column.
The example uses the table(s) created earlier.
SELECT * FROM JSON_TABLE 
(ON (SELECT id, jsonCol, 'CA' AS state, 'USA' AS nation
           FROM my_table)
USING rowexpr('$.schools[*]')
      colexpr('[ {"ordinal" : true},
                 {"jsonpath" : "$.name",
                  "type" : "CHAR ( 12 )"},
                 {"jsonpath" : "$.type",
                  "type" : "VARCHAR ( 12 )"}]'))
AS JT(idcol, ordnum, res1, res2, State, Nation);
Result:
      idcol       ordnum  res1          res2          State  Nation
-----------  -----------  ------------  ------------  -----  ------
          3            0  Lake          elementary    CA     USA
          4            0  Lake          elementary    CA     USA
          3            1  Madison       middle        CA     USA
          4            1  Madison       middle        CA     USA
          3            2  Rancho        high          CA     USA
          4            2  Rancho        high          CA     USA
          3            3  CSUSM         college       CA     USA
          1            4  Lake          elementary    CA     USA
          1            5  Madison       middle        CA     USA
          1            6  Rancho        high          CA     USA
          1            7  UCI           college       CA     USA
          2            8  Lake          elementary    CA     USA
          2            9  Madison       middle        CA     USA
          2           10  Rancho        high          CA     USA
          2           11  Mira Costa    college       CA     USA