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 つのタイプのカラムがサポートされています:

on empty (指定されている場合) は、データが欠落している場合に JSON_TABLE() が何を行うかを決定します (タイプによって異なります)。 この句は、NESTED PATH 句のカラムに一致がなく、NULL 補完行が生成された場合にもトリガーされます。on empty は、次のいずれかの値を取ります:

使用する場合、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 カラムの値が異なるため、異なるオブジェクトの値です。