Example: JSON_TABLE Using Normal 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 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