説明
JSON_TABLE()関数は、リレーショナルテーブルとJSON構造データを結びつける方法を提供します。SQL内で仮想リレーショナルテーブルのようにJSON_TABLE()の出力結果を使用できます。JSON_TABLE()は各JSON値に対して列出力を提供し、JSON内の配列に対しては複数行（列）の出力を提供します。 JSON_TABLE()はSELECTステートメントのFROM句で使用する必要があります。
構文
JSON_TABLE(
expr [FORMAT JSON]
[, json_path_literal]
[ERROR | NULL ON ERROR]
COLUMNS (
[column_name [json_value_return_type] EXISTS [PATH path_literal] [json_exists_on_error_clause] [json_exists_on_emtpy_clause]]
[column_name [json_query_return_type] FORMAT JSON [ALLOW|DISALLOW SCALARS] [json_query_wrapper_clause] [PATH path_literal] [json_query_on_error_clause]]
[column_name [json_value_return_type] [TRUNCATE] [PATH path_literal] [json_exists_on_error_clause] [json_exists_on_emtpy_clause]]
[column_name FOR ORDINALITY]
[NESTED PATH path_literal COLUMNS (
# ネスト構造、親レベルの構文を繰り返す
...
]);
)
)
構文の説明
JSON_TABLE()関数の構文は以下のとおりです：
json_value_return_type：JSON_VALUE()のRETURNING句と同じです。json_query_return_type：JSON_QUERY()のRETURNING句と同じです。FORMAT JSON：オプションです。exprのデータ型がBLOBの場合、この句を使用する必要があります。path_literal：一般的なjson-path文字列です。json_exists_on_emtpy_clause：JSON_EXISTS()のemtpy句と同じです。json_exists_on_error_clause：JSON_EXISTS()のerror句と同じです。json_query_on_error_clause：JSON_QUERY()のerror句と同じです。json_query_wrapper_clause：JSON_QEURY()のWRAPPER句と同じです。FOR ORDINALITY句は、現在の出力行に行番号を提供するために使用され、INT型の列です。ここで出力される値のタイプは4種類あり、ORDINALIY列を除く他の3種類はJSON_VALUE()、JSON_QUERY()、JSON_EXISTS()の機能および動作と比較的似ています。[NESTED PATH ...]句は、ネストされた構造を繰り返します。ERROR句：初期入力exprのエラー処理を指定します。
例
# NESTED COLUMNネストステートメントの使用
DROP TABLE jsonb_table_test;
CREATE TABLE jsonb_table_test (js VARCHAR2 (4000));
DELETE FROM jsonb_table_test;
INSERT INTO jsonb_table_test VALUES (
'[
{"c": [7], "d": [3], "a": 2, "b": [3,4]}
]'
);
obclient> SELECT jt.* FROM jsonb_table_test jtt,
json_table (jtt.js, '$[*]'
columns (
nested path '$.c[*]' COLUMNS (c int path '$'),
nested path '$.d[*]' COLUMNS (d int path '$'),
nested path '$.b[*]' COLUMNS (b int path '$'),
a int path '$.a'
)
) jt;
+------+------+------+------+
| C | D | B | A |
+------+------+------+------+
| NULL | NULL | 3 | 2 |
| NULL | NULL | 4 | 2 |
| NULL | 3 | NULL | 2 |
| 7 | NULL | NULL | 2 |
+------+------+------+------+
4 rows in set
# NESTED COLUMNとORDINALITY列の使用
obclient> INSERT INTO jsonb_table_test VALUES (
'[
{"a": 1, "b": [], "c": []},
{"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
{"a": 3, "b": [1, 2], "c": []},
{"a": 4, "b": [1, 2], "c": [123]}
]'
);
Query OK, 1 row affected
obclient> SELECT jt.* FROM jsonb_table_test jtt,
json_table (jtt.js, '$[*]'
columns (
nested path '$.c[*]' COLUMNS (c int path '$'),
nested path '$.b[*]' COLUMNS (b int path '$'),
n FOR ORDINALITY,
a int path '$.a'
)
) jt;
+------+------+------+------+
| C | B | N | A |
+------+------+------+------+
| NULL | NULL | 1 | 1 |
| NULL | 1 | 2 | 2 |
| NULL | 2 | 2 | 2 |
| NULL | 3 | 2 | 2 |
| 10 | NULL | 2 | 2 |
| NULL | NULL | 2 | 2 |
| 20 | NULL | 2 | 2 |
| NULL | 1 | 3 | 3 |
| NULL | 2 | 3 | 3 |
| NULL | 1 | 4 | 4 |
| NULL | 2 | 4 | 4 |
| 123 | NULL | 4 | 4 |
+------+------+------+------+
12 rows in set
# json_tableのjson_value列を照会し、EMTPY句を使用する
obclient> SELECT jt.* FROM json_table (
'{"data": 123.45}',
'$'
columns (a CLOB path '$.nokey' default 'abc' on EMPTY)
) jt;
+------+
| A |
+------+
| abc |
+------+
1 row in set
# json_tableのjson_value列を照会し、EMTPY句を使用する
obclient> SELECT jt.* FROM json_table (
'{"data": 123.45)',
'$'
columns (a CLOB path '$.nokey' NULL on EMPTY)
) jt;
+------+
| A |
+------+
| NULL |
+------+
1 row in set
obclient> SELECT jt.* FROM
json_table(
'{"details":
[ {"branch_code": "3020300", "products": [
"23D0980000000000",
"23E2980000000000",
"23E3980000000004"]},
{"branch_code": "3090100", "products": [
"23D0980000000000",
"23E2980000000000"]}],
"extras": ["AWUX1201293111", "AWUX1201293112"]
}',
'$.details[*]'
columns (
branch_code varchar2(100) path '$.branch_code',
nested path '$.products[*]' columns (product varchar2(100) path '$[*]")
)
) jt;
+-------------+------------------+
| BRANCH_CODE | PRODUCT |
+-------------+------------------+
| 3020300 | 23D0980000000000 |
| 3020300 | 23E2980000000000 |
| 3020300 | 23E3980000000004 |
| 3090100 | 23D0980000000000 |
| 3090100 | 23E2980000000000 |
+-------------+------------------+
5 rows in set
# json_tableのJSON_EXISTS列を照会する
obclient> SELECT jt.* FROM json_table (
'{
"a": "1997-8-14 11:11:11",
"b" : 123,
"c": "varchar2"
}',
'$' columns (
a date EXISTS path '$.a',
b int EXISTS path '$.b' ERROR ON EMPTY,
c varchar2(32) format json path '$.c' NULL ON EMPTY
)
) jt;
+---------------------+------+----------+
| A | B | C |
+---------------------+------+----------+
| 1997-08-14 11:11:11 | 123 | varchar2 |
+---------------------+------+----------+
1 row in set
# json_tableのjson_query列を照会する
obclient> SELECT jt.* FROM json_table (
'{"data": [1, 2, 3, 3]}',
'$'
columns (a varchar2(1024) FORMAT JSON path '$.data[*]')
) jt;
+--------------+
| A |
+--------------+
| [1, 2, 3, 3] |
+--------------+
1 row in set