15.10 - Example: JSON_TABLE Using Extra Columns - Teradata Database

Teradata Database Teradata JSON

Product
Teradata Database
Release Number
15.10
Published
December 2015
Language
English (United States)
Last Update
2018-06-05
dita:mapPath
TeradataJSON_15_10.ditamap
dita:ditavalPath
ft:empty
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