説明
JSON_TABLE() 関数は、ネストされたJSONデータから必要な情報を抽出するためのサブパス（path）を提供します。
JSON_TABLE() 関数は、JSONデータを半構造化データから構造化データに変換するために使用されます。つまり、JSONドキュメントからデータを抽出し、指定された列を持つリレーショナルテーブルを返します。JSONデータの各部分を仮想テーブルの行と列にマッピングすることで、明確なテーブル構造を作成し、クエリ操作の実行を容易にするだけでなく、ユーザーがこのデータを新しいテーブルやビューに挿入してさらなる処理を行うことも可能にします。
JSON_TABLE() は、各JSON値に対して列出力を提供し、JSON内の配列に対しては複数行（列）の出力も提供します。
構文
JSON_TABLE(expr, path_literal COLUMNS (column_list)) [AS] alias
column_list:
column[, column...]
column:
column_name FOR ORDINALITY
| column_name data_type PATH path_literal [json_value_on_empty_clause] [json_value_on_error_clause]
| column_name data_type EXISTS PATH path_literal
| NESTED [PATH] path_literal COLUMNS (column_list)
json_value_on_empty_clause:
{NULL | DEFAULT json_string | ERROR} ON EMPTY
json_value_on_error_clause:
{NULL | DEFAULT json_string | ERROR} ON ERROR
説明
JSON_TABLE()はSELECTステートメントのFROM句で使用する必要があります。
JSON_TABLE()関数の構文は以下のとおりです：
expr：JSONデータに変換する入力式を指定します。式がJSONデータに変換できない場合、エラーが発生します。path_literal：パスの文字列を指定します。他のデータ型や正しくないパス情報を指定した場合、エラーが発生します。column_list：入力する列の定義情報を指定します。式には少なくとも1つの列定義パラメータを含める必要があります。含まれていない場合、エラーが発生します。column：具体的な列の定義情報を指定します。具体的には以下の4種類に分けられます：column_name FOR ORDINALITY句は、現在の出力行に行番号を提供するために使用されます。ここでcolumn_nameはINT型の列です。column_name data_type PATH path_literal [json_value_on_empty_clause] [json_value_on_error_clause]句は、path_literalで指定されたデータをJSONとして抽出し、列型へ強制的に変換するために使用されます。欠損値がある場合、オプションのjson_value_on_empty_clause句が実行されます。column_name data_type EXISTS PATH path_literal句：path_literalで指定された場所にデータが存在する場合、この列は1を返し、存在しない場合は0を返します。NESTED [PATH] path_literal COLUMNS (column_list)句は、重複するネスト構造を表し、その中でcolumnの4種類の列型を定義し続けることができます。
data_type：データ型を指定します。現在、OceanBaseデータベースのMySQLモードでは、ENUMとSETを除くすべてのデータ型をサポートしています。alias：テーブルのエイリアスを指定します。json_value_on_empty_clause：Pathフィルタリング後のデータがNULL値の場合、ユーザーが選択可能な期待される動作を指定します。選択可能な動作には3種類あります：NULL、ERROR、デフォルト値DEFAULT。NULL ON EMPTY：この列をNULLに設定します。これがデフォルトの動作です。DEFAULT json_string：json_stringを解析して、JSONオブジェクトまたは配列のデフォルト値として代入します。OceanBaseデータベースでは、デフォルト値として任意の型の定数データを許可しています。ERROR ON EMPTY：エラーをスローします。
json_value_on_error_clause：式の実行中にエラーが発生した場合、ユーザーは期待される動作でそのエラーを上書きできます。選択可能な動作には3種類あります：NULL、ERROR、デフォルト値DEFAULT。選択可能な動作はjson_value_on_empty_clauseと同じです。以下のシナリオでは、オプションの
json_value_on_error_clause句が実行されます。exprが正しい形式のJSONデータではない場合JSONパス式を使用してJSONデータを計算する際に非スカラー値が検出された場合
JSONパス式を使用してJSONデータを計算する際にマッチング項目が見つからなかった場合
指定された戻り値のデータ型が小さすぎて戻り値を格納できない場合
例
ユーザー入力データの解析
以下の例は、ユーザーが入力したJSONデータをリレーショナルテーブルに変換するものです。
SELECT * FROM
JSON_TABLE(
'[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
'$[*]' COLUMNS (id FOR ORDINALITY,
jpath VARCHAR(100) PATH '$.a'
DEFAULT '33' ON EMPTY
DEFAULT '66' ON ERROR,
jsn_path JSON PATH '$.a' DEFAULT '{"x":33}' ON EMPTY,
jexst INT EXISTS PATH '$.b')
) AS tt;
この例のクエリ結果は次のとおりです：
+------+-------+-----------+-------+
| id | jpath | jsn_path | jexst |
+------+-------+-----------+-------+
| 1 | 33 | {"x": 33} | 0 |
| 2 | 2 | 2 | 0 |
| 3 | 33 | {"x": 33} | 1 |
| 4 | 0 | 0 | 0 |
| 5 | 66 | [1, 2] | 0 |
+------+-------+-----------+-------+
5 rows in set
テーブルからデータの取得
以下の例は、テーブルからJSONデータを取得し、それを展開して構造化データにするものです。
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INT, jd JSON);
INSERT INTO t1 VALUES (1, '[1,3,5]'),(2,'[2,4,6]');
SELECT id, jt.* FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
この例のクエリ結果は次のとおりです：
+------+------+------+
| id | jid | val |
+------+------+------+
| 1 | 1 | 1 |
| 1 | 2 | 3 |
| 1 | 3 | 5 |
| 2 | 1 | 2 |
| 2 | 2 | 4 |
| 2 | 3 | 6 |
+------+------+------+
6 rows in set
ビューの作成
以下の例は、JSON_TABLE() 関数の戻り値を使用してビューを生成する方法です。
CREATE VIEW v1 AS
SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
'$[*]' COLUMNS ( a INT PATH '$.b')) AS jt;
SELECT * FROM v1;
サンプルクエリの結果は次のとおりです：
+------+
| a |
+------+
| 2 |
+------+
1 row in set
以下の例は、ビュー定義を確認する方法です。
SHOW CREATE VIEW v1;
DROP VIEW v1;
サンプルクエリの結果は次のとおりです：
+------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1 | CREATE VIEW `v1` AS select `jt`.`a` AS `a` from JSON_TABLE('[{\"a\": 1, \"b\": 2}]' , '$[*]' columns (a INTEGER path '$.b' )) jt | utf8mb4 | utf8mb4_general_ci |
+------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set
テーブルへのデータ挿入
以下の例は、JSON_TABLE() 関数の戻り値をテーブルに挿入するものです。
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(id INT, a TINYINT, b VARCHAR(5), c DATE, d DECIMAL);
INSERT INTO t2 SELECT * FROM
JSON_TABLE(JSON_ARRAY(JSON_OBJECT('a', 1, 'b', 'abc'),
JSON_OBJECT('a', 2, 'b', 'abcd'),
JSON_OBJECT('a', 1000, 'b', 'xyz'),
JSON_OBJECT('c', TIME'12:00:00', 'd', 1e308)),
'$[*]' COLUMNS (id FOR ORDINALITY,
a TINYINT PATH '$.a' DEFAULT '111' ON ERROR,
b VARCHAR(5) PATH '$.b' DEFAULT '"ERR"' ON ERROR,
c DATE PATH '$.c' DEFAULT '"2001-01-01"' ON ERROR,
d DECIMAL PATH '$.c' DEFAULT '999' ON ERROR)
) AS jt;
SELECT * FROM t2 ORDER BY id;
DROP TABLE t2;
このクエリの実行結果は次のとおりです：
+------+------+------+------------+-------+
| id | a | b | c | d |
+------+------+------+------------+-------+
| 1 | 1 | abc | NULL | NULL |
| 2 | 2 | abcd | NULL | NULL |
| 3 | 111 | xyz | NULL | NULL |
| 4 | NULL | NULL | 2001-01-01 | 43200 |
+------+------+------+------------+-------+
4 rows in set