12.18.6 JSON テーブル関数

このセクションでは、JSON データを表形式データに変換する JSON 関数について説明します。 MySQL 8.0.4 以降では、このような機能の JSON_TABLE() がサポートされています。

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
JSON ドキュメントからデータを抽出し、指定されたカラムを持つリレーショナルテーブルとして戻します。この関数の完全な構文を次に示します:
```
JSON_TABLE(
    expr,
    path COLUMNS (column_list)
)   [AS] alias

column_list:
    column[, column][, ...]

column:
    name FOR ORDINALITY
    |  name type PATH string path [on_empty] [on_error]
    |  name type EXISTS PATH string path
    |  NESTED [PATH] path COLUMNS (column_list)

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR
```
expr: これは JSON データを返す式です。定数 ('{"a":1}')、カラム (t1.json_data、FROM 句で JSON_TABLE() の前に指定されたテーブル t1)、または関数コール (JSON_EXTRACT(t1.json_data,'$.post.comments')) を指定できます。
path: データソースに適用される JSON パス式。パスに一致する JSON 値を行ソースと呼びます。これはリレーショナルデータの行を生成するために使用されます。 COLUMNS 句は、行ソースを評価し、行ソース内の特定の JSON 値を検索して、リレーショナルデータの行の個々のカラムにそれらの JSON 値を SQL 値として返します。
alias は必須です。テーブルのエイリアスの通常のルールが適用されます (セクション9.2「スキーマオブジェクト名」を参照)。
JSON_TABLE() では、次のリストに示す 4 つのタイプのカラムがサポートされています:
1. name FOR ORDINALITY: このタイプは、COLUMNS 句の行を列挙します。name という名前のカラムは、タイプが UNSIGNED INT で初期値が 1 のカウンタです。これは、CREATE TABLE ステートメントでカラムを AUTO_INCREMENT として指定することと同等で、NESTED [PATH]句によって生成された複数の行に対して同じ値を持つ親行を区別するために使用できます。
2. name type PATH string_path [on_empty] [on_error]: このタイプのカラムは、string_path で指定された値を抽出するために使用されます。type は MySQL スカラーデータ型です (つまり、オブジェクトまたは配カラムにすることはできません)。 JSON_TABLE() は、JSON としてデータを抽出し、MySQL の JSON データに適用される通常の自動型変換を使用して、そのデータをカラムタイプに強制的に変換します。欠損値によって on_empty 句がトリガーされます。オブジェクトまたは配列を保存すると、オプションの on error 句がトリガーされます。これは、文字列'asd'を整数カラムに保存しようとするなど、JSON として保存された値からテーブルのカラムへの強制変換中にエラーが発生した場合にも発生します。
3. name type EXISTS PATH path: このカラムは、path で指定された場所にデータが存在する場合は 1 を返し、それ以外の場合は 0 を返します。type は任意の有効な MySQL データ型にできますが、通常は様々な INT として指定する必要があります。
4. NESTED [PATH] path COLUMNS (column_list): これにより、JSON データ内のネストされたオブジェクトまたは配列が、親オブジェクトまたは配列からの JSON 値とともに単一行にフラット化されます。複数の PATH オプションを使用すると、複数レベルのネストから単一の行に JSON 値を投影できます。
  path は、JSON_TABLE() の親パス行パス、またはネストされたパスの場合の親 NESTED [PATH]句のパスからの相対パスです。
on empty (指定されている場合) は、データが欠落している場合に JSON_TABLE() が何を行うかを決定します (タイプによって異なります)。この句は、NESTED PATH 句のカラムに一致がなく、NULL 補完行が生成された場合にもトリガーされます。on empty は、次のいずれかの値を取ります:
- NULL ON EMPTY: カラムは NULL に設定されています。これはデフォルトの動作です。
- DEFAULT json_string ON EMPTY: 指定された json_string は、有効であるかぎり JSON として解析され、欠落している値のかわりに格納されます。カラムタイプのルールは、デフォルト値にも適用されます。
- ERROR ON EMPTY: エラーがスローされます。
使用する場合、on_error は、次に示すように、対応する結果とともに次のいずれかの値を取ります:
- NULL ON ERROR: カラムは NULL に設定されています。これはデフォルトの動作です。
- DEFAULT json string ON ERROR: json_string は JSON として解析され (有効な場合)、オブジェクトまたは配列のかわりに格納されます。
- ERROR ON ERROR: エラーがスローされます。
MySQL 8.0.20 より前は、NULL ON ERROR または DEFAULT ... ON ERROR で型変換エラーが発生した場合、または暗黙的に指定された場合に警告がスローされました。 MySQL 8.0.20 以降では、これは当てはまりません。 (Bug #30628330)
以前は、ON EMPTY 句と ON ERROR 句をいずれかの順序で指定できました。これにより、SQL 標準へのカウンタが実行されます。このカウンタは、指定されている場合、ON ERROR 句の前にその ON EMPTY を指定する必要があります。このため、MySQL 8.0.20 以降では、ON EMPTY が非推奨になる前に ON ERROR を指定すると、サーバーで警告が発行されます。非標準構文のサポートは、MySQL の将来のバージョンで削除される予定です。
3.14159 を DECIMAL(10,1) カラムに保存するなど、カラムに保存された値が切り捨てられると、ON ERROR オプションとは関係なく警告が発行されます。単一のステートメントで複数の値が切り捨てられた場合、警告は一度のみ発行されます。
MySQL 8.0.21 より前は、この関数に渡された式およびパスが JSON null に解決されると、JSON_TABLE() でエラーが発生しました。 MySQL 8.0.21 以降では、次に示すように、SQL 標準に従って SQL NULL が返されます (Bug #31345503、Bug #99557):
```
mysql> SELECT *
    ->   FROM
    ->     JSON_TABLE(
    ->       '[ {"c1": null} ]',
    ->       '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
    ->     ) as jt;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
```
次のクエリーは、ON EMPTY および ON ERROR の使用方法を示しています。パス"$.a"の {"b":1} に対応する行が空で、[1,2]をスカラーとして保存しようとするとエラーが発生します。これらの行は、次に示す出力で強調表示されます。
```
mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
    ->     "$[*]"
    ->     COLUMNS(
    ->       rowid FOR ORDINALITY,
    ->       ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
    ->       aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
    ->       bx INT EXISTS PATH "$.b"
    ->     )
    ->   ) AS tt;

+-------+------+------------+------+
| rowid | ac   | aj         | bx   |
+-------+------+------------+------+
|     1 | 3    | "3"        |    0 |
|     2 | 2    | 2          |    0 |
|     3 | 111  | {"x": 333} |    1 |
|     4 | 0    | 0          |    0 |
|     5 | 999  | [1, 2]     |    0 |
+-------+------+------------+------+
5 rows in set (0.00 sec)
```
カラム名には、テーブルのカラム名を制御する通常のルールおよび制限が適用されます。セクション9.2「スキーマオブジェクト名」を参照してください。
すべての JSON および JSON パス式の妥当性がチェックされます。どちらのタイプの式も無効であると、エラーが発生します。
COLUMNS キーワードの前にある path の各一致は、結果テーブルの個々の行にマップされます。たとえば、次のクエリーを実行すると、次に示す結果が得られます:
```
mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    ->     "$[*]" COLUMNS(
    ->       xval VARCHAR(100) PATH "$.x",
    ->       yval VARCHAR(100) PATH "$.y"
    ->     )
    ->   ) AS  jt1;

+------+------+
| xval | yval |
+------+------+
| 2    | 8    |
| 3    | 7    |
| 4    | 6    |
+------+------+
```
式"$[*]"は配列の各要素と一致します。パスを変更することで、結果の行をフィルタできます。たとえば、"$[1]"を使用すると、次に示すように、抽出がソースとして使用される JSON 配列の 2 番目の要素に制限されます:
```
mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    ->     "$[1]" COLUMNS(
    ->       xval VARCHAR(100) PATH "$.x",
    ->       yval VARCHAR(100) PATH "$.y"
    ->     )
    ->   ) AS  jt1;

+------+------+
| xval | yval |
+------+------+
| 3    | 7    |
+------+------+
```
カラム定義内では、"$"は一致全体をカラムに渡します。"$.x"および"$.y"は、キー x および y に対応する値のみをその一致内で渡します。詳細は、JSON パス構文を参照してください。
NESTED PATH (または単に NESTED。PATH はオプション) では、所属する COLUMNS 句の一致ごとに一連のレコードが生成されます。一致しない場合、ネストされたパスのすべてのカラムが NULL に設定されます。これにより、最上位の句と NESTED [PATH]の間の外部結合が実装されます。内部結合をエミュレートするには、次に示すように、WHERE 句に適切な条件を適用します:
```
mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
    ->     '$[*]' COLUMNS(
    ->             a INT PATH '$.a',
    ->             NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
    ->            )
    ->    ) AS jt
    -> WHERE b IS NOT NULL;

+------+------+
| a    | b    |
+------+------+
|    1 |   11 |
|    1 |  111 |
|    2 |   22 |
|    2 |  222 |
+------+------+
```
兄弟のネストされたパス (同じ COLUMNS 句内の NESTED [PATH]の複数のインスタンス) は、一度に複数回処理されます。あるネストされたパスがレコードを生成している間、兄弟のネストされたパス式のカラムは NULL に設定されます。つまり、次に示すように、単一の包含 COLUMNS 句内の単一一致のレコードの合計数は合計であり、NESTED [PATH]修飾子によって生成されるすべてのレコードの積ではありません:
```
mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',
    ->     '$[*]' COLUMNS(
    ->         a INT PATH '$.a',
    ->         NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$'),
    ->         NESTED PATH '$.b[*]' COLUMNS (b2 INT PATH '$')
    ->     )
    -> ) AS jt;

+------+------+------+
| a    | b1   | b2   |
+------+------+------+
|    1 |   11 | NULL |
|    1 |  111 | NULL |
|    1 | NULL |   11 |
|    1 | NULL |  111 |
|    2 |   22 | NULL |
|    2 |  222 | NULL |
|    2 | NULL |   22 |
|    2 | NULL |  222 |
+------+------+------+
```
FOR ORDINALITY カラムは、COLUMNS 句によって生成されたレコードを列挙し、ネストされたパスの親レコードを区別するために使用できます。特に、親レコードの値が同じ場合は、次のようになります:
```
mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": "a_val",
    '>       "b": [{"c": "c_val", "l": [1,2]}]},
    '>     {"a": "a_val",
    '>       "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',
    ->     '$[*]' COLUMNS(
    ->       top_ord FOR ORDINALITY,
    ->       apath VARCHAR(10) PATH '$.a',
    ->       NESTED PATH '$.b[*]' COLUMNS (
    ->         bpath VARCHAR(10) PATH '$.c',
    ->         ord FOR ORDINALITY,
    ->         NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')
    ->         )
    ->     )
    -> ) as jt;

+---------+---------+---------+------+-------+
| top_ord | apath   | bpath   | ord  | lpath |
+---------+---------+---------+------+-------+
|       1 |  a_val  |  c_val  |    1 | 1     |
|       1 |  a_val  |  c_val  |    1 | 2     |
|       2 |  a_val  |  c_val  |    1 | 11    |
|       2 |  a_val  |  c_val  |    2 | 22    |
+---------+---------+---------+------+-------+
```
ソースドキュメントには 2 つの要素の配列が含まれており、これらの各要素は 2 つの行を生成します。 apath と bpath の値は、結果セット全体で同じです。つまり、lpath 値が同じ親から取得されたのか、別の親から取得されたのかを判断するために使用できません。 ord カラムの値は、top_ord が 1 に等しいレコードのセットと同じままであるため、これらの 2 つの値は単一オブジェクトからの値です。残りの 2 つの値は、ord カラムの値が異なるため、異なるオブジェクトの値です。