Example: JSON_TABLE Using Extra Columns

Teradata Vantageā„¢ JSON Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K
The example shows JSON_TABLE using extra columns, in addition to the required columns of ID and JSON document. The column_expression_literal parameter requires a mapping of the columns in the row_expression_literal to the columns of the output table of this function, as this example demonstrates. To simplify this example, constants are used for the state and nation columns.
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 WHERE id=1)
USING rowexpr('$.schools[*]') 
      colexpr('[ {"jsonpath" : "$.name",
                  "type" : "CHAR(20)"},
                 {"jsonpath" : "$.type",
                  "type" : "VARCHAR(20)"}]')
) AS JT(id, name, "type", State, Nation);
Result:
id  name     type               State  Nation
------------------------------------------------
1   Lake     elementary         CA     USA
1   Madison  middle             CA     USA
1   Rancho   high               CA     USA
1   UCI      college            CA     USA