次の例では、序数列を使用するJSON_TABLEを示します。
例では、以前に作成したテーブルを使用します。
SELECT * FROM JSON_TABLE
(ON (SELECT id, jsonCol, 'CA' AS state, 'USA' AS nation
FROM my_table)
USING rowexpr('$.schools[*]')
colexpr('[ {"ordinal" : true},
{"jsonpath" : "$.name",
"type" : "CHAR ( 12 )"},
{"jsonpath" : "$.type",
"type" : "VARCHAR ( 12 )"}]'))
AS JT(idcol, ordnum, res1, res2, State, Nation);
結果:
idcol ordnum res1 res2 State Nation
----------- ----------- ------------ ------------ ----- ------
3 0 Lake elementary CA USA
4 0 Lake elementary CA USA
3 1 Madison middle CA USA
4 1 Madison middle CA USA
3 2 Rancho high CA USA
4 2 Rancho high CA USA
3 3 CSUSM college CA USA
1 4 Lake elementary CA USA
1 5 Madison middle CA USA
1 6 Rancho high CA USA
1 7 UCI college CA USA
2 8 Lake elementary CA USA
2 9 Madison middle CA USA
2 10 Rancho high CA USA
2 11 Mira Costa college CA USA