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