このセクションでは、JSON データを表形式データに変換する JSON 関数について説明します。 MySQL 8.0.4 以降では、このような機能の JSON_TABLE() がサポートされています。
-
JSON_TABLE(expr,pathCOLUMNS (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 ERRORexpr: これは 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 つのタイプのカラムがサポートされています:nameFOR ORDINALITYCOLUMNS句の行を列挙します。nameという名前のカラムは、タイプがUNSIGNED INTで初期値が 1 のカウンタです。 これは、CREATE TABLEステートメントでカラムをAUTO_INCREMENTとして指定することと同等で、NESTED [PATH]句によって生成された複数の行に対して同じ値を持つ親行を区別するために使用できます。nametypePATHstring_path[on_empty] [on_error]string_pathで指定された値を抽出するために使用されます。typeは MySQL スカラーデータ型です (つまり、オブジェクトまたは配カラムにすることはできません)。JSON_TABLE()は、JSON としてデータを抽出し、MySQL の JSON データに適用される通常の自動型変換を使用して、そのデータをカラムタイプに強制的に変換します。 欠損値によってon_empty句がトリガーされます。 オブジェクトまたは配列を保存すると、オプションのon error句がトリガーされます。これは、文字列'asd'を整数カラムに保存しようとするなど、JSON として保存された値からテーブルのカラムへの強制変換中にエラーが発生した場合にも発生します。nametypeEXISTS PATHpathpathで指定された場所にデータが存在する場合は 1 を返し、それ以外の場合は 0 を返します。typeは任意の有効な MySQL データ型にできますが、通常は様々なINTとして指定する必要があります。-
NESTED [PATH]: これにより、JSON データ内のネストされたオブジェクトまたは配列が、親オブジェクトまたは配列からの JSON 値とともに単一行にフラット化されます。 複数のpathCOLUMNS (column_list)PATHオプションを使用すると、複数レベルのネストから単一の行に JSON 値を投影できます。pathは、JSON_TABLE()の親パス行パス、またはネストされたパスの場合の親NESTED [PATH]句のパスからの相対パスです。
on empty(指定されている場合) は、データが欠落している場合にJSON_TABLE()が何を行うかを決定します (タイプによって異なります)。 この句は、NESTED PATH句のカラムに一致がなく、NULL補完行が生成された場合にもトリガーされます。on emptyは、次のいずれかの値を取ります:NULL ON EMPTY: カラムはNULLに設定されています。これはデフォルトの動作です。DEFAULT: 指定されたjson_stringON EMPTYjson_stringは、有効であるかぎり JSON として解析され、欠落している値のかわりに格納されます。 カラムタイプのルールは、デフォルト値にも適用されます。ERROR ON EMPTY: エラーがスローされます。
使用する場合、
on_errorは、次に示すように、対応する結果とともに次のいずれかの値を取ります:NULL ON ERROR: カラムはNULLに設定されています。これはデフォルトの動作です。DEFAULT:json stringON ERRORjson_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 標準に従って SQLNULLが返されます (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カラムの値が異なるため、異なるオブジェクトの値です。