説明
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 FOR句は、現在の出力行に行番号を提供するために使用されます。ここで、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：パスフィルタリングされたデータが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