Example: JSON_TABLE Using Extra Columns - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
gzn1554761068186.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantageā„¢
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