15.10 - Example: JSON_TABLE Using Normal Column - 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 normal column.
The example uses the table(s) created earlier.
SELECT * FROM JSON_Table 
(ON (SELECT id, jsonCol FROM my_table WHERE id=1)
USING rowexpr('$.schools[*]')
               colexpr('[ {"jsonpath" : "$.name",
                           "type" : "CHAR(20)"},
                          {"jsonpath" : "$.type",
                           "type" : "VARCHAR(20)"}]')
) AS JT(id, schoolName, "type");
Result:
id  schoolName    type
------------------------------
1   Lake          elementary
1   Madison       middle
1   Rancho        high
1   UCI           college
The example shows JSON_Table using the fromRoot attribute.
The example uses the table(s) created earlier.
SELECT * FROM JSON_Table
( ON (SELECT id, jsonCol FROM my_table WHERE id=1)
USING rowexpr('$.schools[*]')
      colexpr('[ {"jsonpath" : "$.name",
                  "type" : "CHAR(20)"},
                 {"jsonpath" : "$.type",
                  "type" : "VARCHAR(20)"},
                 {"jsonpath" : "$.name",
                  "type" : "VARCHAR(20)",
                  "fromRoot":true} ]')
) AS JT(id, schoolName, "type", studentName);
Result:
id  schoolName     type               studentName
--------------------------------------------------
1   Lake           elementary         Cameron
1   Madison        middle             Cameron
1   Rancho         high               Cameron
1   UCI            college            Cameron