このページは機械翻訳したものです。
このセクションの関数では、JSON 値に対して検索または比較操作を実行して、JSON 値からデータを抽出したり、データが JSON 値内の場所に存在するかどうかをレポートしたり、JSON 値内のデータへのパスをレポートします。 ここでは、MEMBER OF() 演算子についても説明します。
JSON_CONTAINS(target,candidate[,path])特定の
candidateJSON ドキュメントがtargetJSON ドキュメント内に含まれているかどうか、またはpath引数が指定されているかどうか (候補がターゲット内の特定のパスで見つかったかどうか) を 1 または 0 を返して示します。 いずれかの引数がNULLの場合、またはパス引数がターゲットドキュメントのセクションを識別しない場合、NULLを返します。targetまたはcandidateが有効な JSON ドキュメントでない場合、またはpath引数が有効なパス式でないか、*または**ワイルドカードが含まれている場合は、エラーが発生します。パスにデータが存在するかどうかのみを確認するには、かわりに
JSON_CONTAINS_PATH()を使用します。次のルールは包含を定義します:
候補スカラーは、比較可能で等しい場合にのみターゲットスカラーに含まれます。 同じ
JSON_TYPE()型を持つ場合、2 つのスカラー値が比較可能ですが、INTEGER型とDECIMAL型の値も相互に比較可能である点が異なります。候補配列は、候補のすべての要素がターゲットの一部の要素に含まれている場合にのみ、ターゲット配列に含まれます。
候補がターゲットの一部の要素に含まれている場合にのみ、候補の非配列がターゲット配列に含まれます。
候補オブジェクトがターゲットオブジェクトに含まれるのは、候補の各キーに対して同じ名前のキーがターゲットにあり、候補キーに関連付けられた値がターゲットキーに関連付けられた値に含まれている場合のみです。
それ以外の場合、候補値はターゲットドキュメントに含まれません。
MySQL 8.0.17 以降、
InnoDBテーブルでJSON_CONTAINS()を使用するクエリーは、複数値インデックスを使用して最適化できます。詳細は、複数値インデックス を参照してください。mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';mysql> SET @j2 = '1';mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');+-------------------------------+| JSON_CONTAINS(@j, @j2, '$.a') |+-------------------------------+| 1 |+-------------------------------+mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');+-------------------------------+| JSON_CONTAINS(@j, @j2, '$.b') |+-------------------------------+| 0 |+-------------------------------+mysql> SET @j2 = '{"d": 4}';mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');+-------------------------------+| JSON_CONTAINS(@j, @j2, '$.a') |+-------------------------------+| 0 |+-------------------------------+mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');+-------------------------------+| JSON_CONTAINS(@j, @j2, '$.c') |+-------------------------------+| 1 |+-------------------------------+JSON_CONTAINS_PATH(json_doc,one_or_all,path[,path] ...)JSON ドキュメントに指定されたパスのデータが含まれているかどうかを示す 0 または 1 を返します。 引数のいずれかが
NULLである場合は、NULLを返します。json_doc引数が有効な JSON ドキュメントでない場合、path引数が有効なパス式でない場合、またはone_or_allが'one'または'all'でない場合は、エラーが発生します。パスで特定の値を確認するには、かわりに
JSON_CONTAINS()を使用します。指定されたパスがドキュメント内に存在しない場合、戻り値は 0 です。 それ以外の場合、戻り値は
one_or_all引数によって異なります:'one': ドキュメント内に少なくとも 1 つのパスが存在する場合は 1、それ以外の場合は 0。'all': ドキュメント内にすべてのパスが存在する場合は 1、それ以外の場合は 0。
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');+---------------------------------------------+| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |+---------------------------------------------+| 1 |+---------------------------------------------+mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');+---------------------------------------------+| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |+---------------------------------------------+| 0 |+---------------------------------------------+mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');+----------------------------------------+| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |+----------------------------------------+| 1 |+----------------------------------------+mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');+----------------------------------------+| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |+----------------------------------------+| 0 |+----------------------------------------+JSON_EXTRACT(json_doc,path[,path] ...)path引数に一致するドキュメントの一部から選択された JSON ドキュメントからデータを返します。 いずれかの引数がNULLの場合、またはドキュメント内の値を特定するパスがない場合は、NULLを返します。json_doc引数が有効な JSON ドキュメントでない場合、またはpath引数が有効なパス式でない場合は、エラーが発生します。戻り値は、
path引数に一致するすべての値で構成されます。 これらの引数が複数の値を返す可能性がある場合、一致した値は、それらを生成したパスに対応する順序で配列として自動ラップされます。 それ以外の場合、戻り値は単一の一致値です。mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');+--------------------------------------------+| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |+--------------------------------------------+| 20 |+--------------------------------------------+mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');+----------------------------------------------------+| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |+----------------------------------------------------+| [20, 10] |+----------------------------------------------------+mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');+-----------------------------------------------+| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |+-----------------------------------------------+| [30, 40] |+-----------------------------------------------+MySQL では、この関数の短縮形として
->演算子がサポートされており、左側がJSONカラム識別子 (式ではなく) であり、右側がカラム内で照合される JSON パスである 2 つの引数で使用されます。column->path->演算子は、左側のカラム識別子と JSON ドキュメント (カラム値) に対して評価される右側の JSON パスの 2 つの引数で使用されるJSON_EXTRACT()関数のエイリアスとして機能します。 このような式は、SQL ステートメントのどこにあるかにかかわらず、カラム識別子のかわりに使用できます。次に示す 2 つの
SELECTステートメントでは、同じ出力が生成されます:mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g > FROM jemp > WHERE JSON_EXTRACT(c, "$.id") > 1 > ORDER BY JSON_EXTRACT(c, "$.name");+-------------------------------+-----------+------+| c | c->"$.id" | g |+-------------------------------+-----------+------+| {"id": "3", "name": "Barney"} | "3" | 3 || {"id": "4", "name": "Betty"} | "4" | 4 || {"id": "2", "name": "Wilma"} | "2" | 2 |+-------------------------------+-----------+------+3 rows in set (0.00 sec)mysql> SELECT c, c->"$.id", g > FROM jemp > WHERE c->"$.id" > 1 > ORDER BY c->"$.name";+-------------------------------+-----------+------+| c | c->"$.id" | g |+-------------------------------+-----------+------+| {"id": "3", "name": "Barney"} | "3" | 3 || {"id": "4", "name": "Betty"} | "4" | 4 || {"id": "2", "name": "Wilma"} | "2" | 2 |+-------------------------------+-----------+------+3 rows in set (0.00 sec)次に示すように、この機能は
SELECTに限定されません:mysql> ALTER TABLE jemp ADD COLUMN n INT;Query OK, 0 rows affected (0.68 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4";Query OK, 1 row affected (0.04 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> SELECT c, c->"$.id", g, n > FROM jemp > WHERE JSON_EXTRACT(c, "$.id") > 1 > ORDER BY c->"$.name";+-------------------------------+-----------+------+------+| c | c->"$.id" | g | n |+-------------------------------+-----------+------+------+| {"id": "3", "name": "Barney"} | "3" | 3 | NULL || {"id": "4", "name": "Betty"} | "4" | 4 | 1 || {"id": "2", "name": "Wilma"} | "2" | 2 | NULL |+-------------------------------+-----------+------+------+3 rows in set (0.00 sec)mysql> DELETE FROM jemp WHERE c->"$.id" = "4";Query OK, 1 row affected (0.04 sec)mysql> SELECT c, c->"$.id", g, n > FROM jemp > WHERE JSON_EXTRACT(c, "$.id") > 1 > ORDER BY c->"$.name";+-------------------------------+-----------+------+------+| c | c->"$.id" | g | n |+-------------------------------+-----------+------+------+| {"id": "3", "name": "Barney"} | "3" | 3 | NULL || {"id": "2", "name": "Wilma"} | "2" | 2 | NULL |+-------------------------------+-----------+------+------+2 rows in set (0.00 sec)(前述のテーブルの作成および移入に使用されるステートメントについては、JSON カラムインデックスを提供するための生成されたカラムのインデックス付け を参照してください。)
これは、次に示すように JSON 配列値でも機能します:
mysql> CREATE TABLE tj10 (a JSON, b INT);Query OK, 0 rows affected (0.26 sec)mysql> INSERT INTO tj10 > VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);Query OK, 1 row affected (0.04 sec)mysql> SELECT a->"$[4]" FROM tj10;+--------------+| a->"$[4]" |+--------------+| 44 || [22, 44, 66] |+--------------+2 rows in set (0.00 sec)mysql> SELECT * FROM tj10 WHERE a->"$[0]" = 3;+------------------------------+------+| a | b |+------------------------------+------+| [3, 10, 5, 17, 44] | 33 || [3, 10, 5, 17, [22, 44, 66]] | 0 |+------------------------------+------+2 rows in set (0.00 sec)ネストされた配列がサポートされています。 次に示すように、ターゲット JSON ドキュメントに一致するキーが見つからない場合、
->を使用する式はNULLとして評価されます:mysql> SELECT * FROM tj10 WHERE a->"$[4][1]" IS NOT NULL;+------------------------------+------+| a | b |+------------------------------+------+| [3, 10, 5, 17, [22, 44, 66]] | 0 |+------------------------------+------+mysql> SELECT a->"$[4][1]" FROM tj10;+--------------+| a->"$[4][1]" |+--------------+| NULL || 44 |+--------------+2 rows in set (0.00 sec)これは、
JSON_EXTRACT()を使用している場合と同じ動作です:mysql> SELECT JSON_EXTRACT(a, "$[4][1]") FROM tj10;+----------------------------+| JSON_EXTRACT(a, "$[4][1]") |+----------------------------+| NULL || 44 |+----------------------------+2 rows in set (0.00 sec)column->>pathこれは、改善された引用符で囲まれていない抽出演算子です。
->演算子は単に値を抽出するだけですが、->>演算子は抽出された結果を引用符で囲みません。 つまり、JSONカラム値がcolumnで、パス式がpathの場合、次の 3 つの式は同じ値を返します:JSON_UNQUOTE(JSON_EXTRACT(column,path) )JSON_UNQUOTE(column->path)column->>path
->>演算子は、JSON_UNQUOTE(JSON_EXTRACT())が許可される場所であればどこでも使用できます。 これには、SELECTリスト、WHERE句とHAVING句、ORDER BY句とGROUP BY句が含まれます (これらに限定されません)。次のいくつかのステートメントは、mysql クライアントの他の式と同等の
->>演算子を示しています:mysql> SELECT * FROM jemp WHERE g > 2;+-------------------------------+------+| c | g |+-------------------------------+------+| {"id": "3", "name": "Barney"} | 3 || {"id": "4", "name": "Betty"} | 4 |+-------------------------------+------+2 rows in set (0.01 sec)mysql> SELECT c->'$.name' AS name -> FROM jemp WHERE g > 2;+----------+| name |+----------+| "Barney" || "Betty" |+----------+2 rows in set (0.00 sec)mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name -> FROM jemp WHERE g > 2;+--------+| name |+--------+| Barney || Betty |+--------+2 rows in set (0.00 sec)mysql> SELECT c->>'$.name' AS name -> FROM jemp WHERE g > 2;+--------+| name |+--------+| Barney || Betty |+--------+2 rows in set (0.00 sec)前述の一連の例で
jempテーブルの作成および移入に使用される SQL ステートメントについては、JSON カラムインデックスを提供するための生成されたカラムのインデックス付け を参照してください。この演算子は、次に示すように JSON 配列でも使用できます:
mysql> CREATE TABLE tj10 (a JSON, b INT);Query OK, 0 rows affected (0.26 sec)mysql> INSERT INTO tj10 VALUES -> ('[3,10,5,"x",44]', 33), -> ('[3,10,5,17,[22,"y",66]]', 0);Query OK, 2 rows affected (0.04 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> SELECT a->"$[3]", a->"$[4][1]" FROM tj10;+-----------+--------------+| a->"$[3]" | a->"$[4][1]" |+-----------+--------------+| "x" | NULL || 17 | "y" |+-----------+--------------+2 rows in set (0.00 sec)mysql> SELECT a->>"$[3]", a->>"$[4][1]" FROM tj10;+------------+---------------+| a->>"$[3]" | a->>"$[4][1]" |+------------+---------------+| x | NULL || 17 | y |+------------+---------------+2 rows in set (0.00 sec)->と同様に、次の例に示すように、->>演算子は常にEXPLAINの出力で展開されます:mysql> EXPLAIN SELECT c->>'$.name' AS name -> FROM jemp WHERE g > 2\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: jemp partitions: NULL type: rangepossible_keys: i key: i key_len: 5 ref: NULL rows: 2 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G*************************** 1. row *************************** Level: Note Code: 1003Message: /* select#1 */ selectjson_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from`jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2)1 row in set (0.00 sec)これは、同じ状況で MySQL が
->演算子を拡張する方法と似ています。JSON_KEYS(json_doc[,path])JSON オブジェクトの最上位値からキーを JSON 配列として返します。
path引数が指定されている場合は、選択されたパスの最上位キーを返します。 いずれかの引数がNULLの場合、json_doc引数がオブジェクトでない場合、またはpath(指定されている場合) がオブジェクトを検出しない場合に、NULLを返します。json_doc引数が有効な JSON ドキュメントでないか、path引数が有効なパス式でないか、*または**ワイルドカードが含まれている場合、エラーが発生します。選択したオブジェクトが空の場合、結果配列は空です。 最上位の値にネストされたサブオブジェクトがある場合、戻り値にはそれらのサブオブジェクトのキーは含まれません。
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');+---------------------------------------+| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |+---------------------------------------+| ["a", "b"] |+---------------------------------------+mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');+----------------------------------------------+| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |+----------------------------------------------+| ["c"] |+----------------------------------------------+JSON_OVERLAPS(json_doc1,json_doc2)2 つの JSON ドキュメントを比較します。 2 つのドキュメントに共通のキーと値のペアまたは配列要素がある場合、true (1) を返します。 両方の引数がスカラーの場合、この関数は単純な等価性テストを実行します。
この関数は
JSON_CONTAINS()と同等の役割を果たします。これには、検索対象の配列のすべての要素が検索対象の配列に存在する必要があります。 したがって、JSON_CONTAINS()は検索キーに対してAND操作を実行し、JSON_OVERLAPS()はOR操作を実行します。WHERE句でJSON_OVERLAPS()を使用するInnoDBテーブルの JSON カラムに対するクエリーは、複数値インデックスを使用して最適化できます。複数値インデックス では、詳細な情報と例を示します。2 つの配列を比較する場合、
JSON_OVERLAPS()は共通の配列要素を共有すると true を返し、共有しない場合は false を返します:mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");+---------------------------------------+| JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]") |+---------------------------------------+| 1 |+---------------------------------------+1 row in set (0.00 sec)mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]");+---------------------------------------+| JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]") |+---------------------------------------+| 1 |+---------------------------------------+1 row in set (0.00 sec)mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");+---------------------------------------+| JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]") |+---------------------------------------+| 0 |+---------------------------------------+1 row in set (0.00 sec)部分一致は、次に示すように一致なしとして処理されます:
mysql> SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');+-----------------------------------------------------+| JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]') |+-----------------------------------------------------+| 0 |+-----------------------------------------------------+1 row in set (0.00 sec)オブジェクトを比較する場合、共通のキーと値のペアが 1 つ以上あると、結果は true になります。
mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');+-----------------------------------------------------------------------+| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}') |+-----------------------------------------------------------------------+| 1 |+-----------------------------------------------------------------------+1 row in set (0.00 sec)mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}');+-----------------------------------------------------------------------+| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}') |+-----------------------------------------------------------------------+| 0 |+-----------------------------------------------------------------------+1 row in set (0.00 sec)関数の引数として 2 つのスカラーが使用されている場合、
JSON_OVERLAPS()は等価性の単純なテストを実行します:mysql> SELECT JSON_OVERLAPS('5', '5');+-------------------------+| JSON_OVERLAPS('5', '5') |+-------------------------+| 1 |+-------------------------+1 row in set (0.00 sec)mysql> SELECT JSON_OVERLAPS('5', '6');+-------------------------+| JSON_OVERLAPS('5', '6') |+-------------------------+| 0 |+-------------------------+1 row in set (0.00 sec)スカラーを配列と比較する場合、
JSON_OVERLAPS()はスカラーを配列要素として処理しようとします。 この例では、次に示すように、2 番目の引数6が[6]として解釈されます:mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '6');+---------------------------------+| JSON_OVERLAPS('[4,5,6,7]', '6') |+---------------------------------+| 1 |+---------------------------------+1 row in set (0.00 sec)この関数は型変換を実行しません:
mysql> SELECT JSON_OVERLAPS('[4,5,"6",7]', '6');+-----------------------------------+| JSON_OVERLAPS('[4,5,"6",7]', '6') |+-----------------------------------+| 0 |+-----------------------------------+1 row in set (0.00 sec)mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"');+-----------------------------------+| JSON_OVERLAPS('[4,5,6,7]', '"6"') |+-----------------------------------+| 0 |+-----------------------------------+1 row in set (0.00 sec)JSON_OVERLAPS()が MySQL 8.0.17 に追加されました。JSON_SEARCH(json_doc,one_or_all,search_str[,escape_char[,path] ...])JSON ドキュメント内の指定された文字列へのパスを返します。
json_doc、search_strまたはpath引数のいずれかがNULLの場合、ドキュメント内にpathが存在しない場合、またはsearch_strが見つからない場合は、NULLを返します。json_doc引数が有効な JSON ドキュメントでない場合、path引数が有効なパス式でない場合、one_or_allが'one'または'all'でない場合、またはescape_charが定数式でない場合は、エラーが発生します。one_or_all引数は、次のように検索に影響します:'one': 最初の一致の後に検索が終了し、1 つのパス文字列が返されます。 一致が最初に考慮されるのは未定義です。'all': 検索では、重複するパスが含まれないように、一致するすべてのパス文字列が返されます。 複数の文字列がある場合は、配列として自動ラップされます。 配列要素の順序が未定義です。
search_str検索文字列引数内では、%および_文字はLIKE演算子と同様に機能:%は任意の数の文字 (ゼロ文字を含む) に一致し、_は完全に 1 文字に一致します。検索文字列にリテラル
%または_文字を指定するには、その前にエスケープ文字を付けます。escape_char引数がない場合、またはNULLの場合、デフォルトは\です。 それ以外の場合、escape_charは空または 1 文字の定数である必要があります。一致およびエスケープ文字の動作の詳細は、セクション12.8.1「文字列比較関数および演算子」 の
LIKEの説明を参照してください。 エスケープ文字処理の場合、LIKEの動作との違いは、JSON_SEARCH()のエスケープ文字は、実行時だけでなく、コンパイル時に定数に評価される必要があることです。 たとえば、JSON_SEARCH()がプリペアドステートメントで使用され、?パラメータを使用してescape_char引数が指定されている場合、パラメータ値は実行時には一定ですが、コンパイル時には一定ではありません。mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');+-------------------------------+| JSON_SEARCH(@j, 'one', 'abc') |+-------------------------------+| "$[0]" |+-------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');+-------------------------------+| JSON_SEARCH(@j, 'all', 'abc') |+-------------------------------+| ["$[0]", "$[2].x"] |+-------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');+-------------------------------+| JSON_SEARCH(@j, 'all', 'ghi') |+-------------------------------+| NULL |+-------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '10');+------------------------------+| JSON_SEARCH(@j, 'all', '10') |+------------------------------+| "$[1][0].k" |+------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');+-----------------------------------------+| JSON_SEARCH(@j, 'all', '10', NULL, '$') |+-----------------------------------------+| "$[1][0].k" |+-----------------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');+--------------------------------------------+| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |+--------------------------------------------+| "$[1][0].k" |+--------------------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');+---------------------------------------------+| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |+---------------------------------------------+| "$[1][0].k" |+---------------------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');+-------------------------------------------------+| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |+-------------------------------------------------+| "$[1][0].k" |+-------------------------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');+--------------------------------------------+| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |+--------------------------------------------+| "$[1][0].k" |+--------------------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');+-----------------------------------------------+| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |+-----------------------------------------------+| "$[1][0].k" |+-----------------------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');+---------------------------------------------+| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |+---------------------------------------------+| "$[2].x" |+---------------------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');+-------------------------------+| JSON_SEARCH(@j, 'all', '%a%') |+-------------------------------+| ["$[0]", "$[2].x"] |+-------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');+-------------------------------+| JSON_SEARCH(@j, 'all', '%b%') |+-------------------------------+| ["$[0]", "$[2].x", "$[3].y"] |+-------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');+---------------------------------------------+| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |+---------------------------------------------+| "$[0]" |+---------------------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');+---------------------------------------------+| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |+---------------------------------------------+| "$[2].x" |+---------------------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');+---------------------------------------------+| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |+---------------------------------------------+| NULL |+---------------------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');+-------------------------------------------+| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |+-------------------------------------------+| NULL |+-------------------------------------------+mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');+-------------------------------------------+| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |+-------------------------------------------+| "$[3].y" |+-------------------------------------------+ワイルドカード演算子
*および**を制御するルールなど、MySQL でサポートされている JSON パス構文の詳細は、JSON パス構文 を参照してください。JSON_VALUE(json_doc,path)指定されたドキュメントで指定されたパスにある JSON ドキュメントから値を抽出し、抽出された値を返します。オプションで、必要なタイプに変換します。 完全な構文は次のとおりです:
JSON_VALUE(json_doc,path [RETURNINGtype] [on_empty] [on_error])on_empty: {NULL | ERROR | DEFAULTvalue} ON EMPTYon_error: {NULL | ERROR | DEFAULTvalue} ON ERRORjson_docは有効な JSON ドキュメントです。pathは、ドキュメント内の場所を指す JSON パスです。typeは、次のいずれかのデータ型です:FLOATDOUBLEDECIMALSIGNEDUNSIGNEDDATETIMEDATETIMEYEAR(MySQL 8.0.22 以降)1 桁または 2 桁の
YEAR値はサポートされていません。CHARJSON
リストされている型は、
CAST()関数でサポートされている (配列以外の) 型と同じです。RETURNING句で指定されていない場合、JSON_VALUE()関数の戻り型はVARCHAR(512)です。 戻り型に文字セットが指定されていない場合、JSON_VALUE()はバイナリ照合順序でutf8mb4を使用します。これは大/小文字が区別されます。utf8mb4が結果の文字セットとして指定されている場合、サーバーはこの文字セットのデフォルト照合順序を使用しますが、大文字と小文字は区別されません。指定したパスのデータが JSON null リテラルで構成されているか、JSON NULL リテラルに解決されると、関数は SQL
NULLを戻します。on_emptyは、指定されたパスにデータが見つからない場合のJSON_VALUE()の動作を決定します。この句には、次のいずれかの値を指定します:NULL ON EMPTY: この関数はNULLを戻します。これはデフォルトのON EMPTY動作です。DEFAULT: 指定されたvalueON EMPTYvalueが返されます。 値の型は戻り値の型と一致する必要があります。ERROR ON EMPTY: この関数はエラーをスローします。
使用する場合、
on_errorは、次に示すように、エラーが発生したときに対応する結果とともに次のいずれかの値を取ります:NULL ON ERROR:JSON_VALUE()はNULLを返します。これは、ON ERROR句が使用されていない場合のデフォルトの動作です。DEFAULT: これは返される値です。その値は戻り型の値と一致する必要があります。valueON ERRORERROR ON ERROR: エラーがスローされます。
ON EMPTYを使用する場合は、ON ERROR句の前に置く必要があります。 間違った順序で指定すると、構文エラーが発生します。エラー処理. 通常、エラーは
JSON_VALUE()によって次のように処理されます:すべての JSON 入力 (ドキュメントおよびパス) の有効性がチェックされます。 いずれかが有効でない場合、
ON ERROR句をトリガーせずに SQL エラーがスローされます。ON ERRORは、次のいずれかのイベントが発生するたびにトリガーされます:JSON ドキュメント内の複数の場所に解決されるパスから生成されたオブジェクトまたは配列を抽出しようとしています
'asdf'をUNSIGNED値に変換しようとするなどの変換エラー値の切捨て
NULL ON ERRORまたはDEFAULT ... ON ERRORが指定されている場合でも、変換エラーによって常に警告がトリガーされます。ON EMPTY句は、ソース JSON ドキュメント (expr) の指定した場所 (path) にデータが含まれていない場合にトリガーされます。
JSON_VALUE()は、MySQL 8.0.21 で導入されました。例. ここでは、2 つの簡単な例を示します:
mysql> SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');+--------------------------------------------------------------+| JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') |+--------------------------------------------------------------+| Joe |+--------------------------------------------------------------+mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price' -> RETURNING DECIMAL(4,2)) AS price;+-------+| price |+-------+| 49.95 |+-------+SELECT JSON_VALUE(というステートメントは、次のステートメントと同等です:json_doc,pathRETURNINGtype)SELECT CAST( JSON_UNQUOTE( JSON_EXTRACT(json_doc,path) ) AStype);JSON_VALUE()では、多くの場合、生成されたカラムを作成してから生成されたカラムのインデックスを作成する必要がなくなるため、JSON カラムのインデックスの作成が簡略化されます。 これを行うには、次に示すように、JSONカラムを含むテーブルt1を作成するときに、そのカラムで動作するJSON_VALUE()を使用する式にインデックスを作成します (そのカラムの値と一致するパスを使用):CREATE TABLE t1( j JSON, INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) ));次の
EXPLAIN出力は、WHERE句でインデックス式を使用するt1に対するクエリーで、作成されたインデックスが使用されることを示しています:mysql> EXPLAIN SELECT * FROM t1 -> WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: refpossible_keys: i1 key: i1 key_len: 9 ref: const rows: 1 filtered: 100.00 Extra: NULLこれは、次のように、生成されたカラム (JSON カラムインデックスを提供するための生成されたカラムのインデックス付け を参照) にインデックスを使用してテーブル
t2を作成するのとほぼ同じ結果になります:CREATE TABLE t2 ( j JSON, g INT GENERATED ALWAYS AS (j->"$.id"), INDEX i1 (j));生成されたカラムを参照する、このテーブルに対するクエリーの
EXPLAIN出力は、テーブルt1に対する前述のクエリーと同じ方法でインデックスが使用されることを示しています:mysql> EXPLAIN SELECT * FROM t2 WHERE g = 123\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: refpossible_keys: i1 key: i1 key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL生成されたカラムに対するインデックスを使用した
JSONカラムの間接インデックス付けの詳細は、JSON カラムインデックスを提供するための生成されたカラムのインデックス付け を参照してください。valueMEMBER OF(json_array)valueがjson_arrayの要素である場合は true (1) を返し、それ以外の場合は false (0) を返します。valueはスカラーまたは JSON 文書である必要があります。スカラーの場合、演算子は JSON 配列の要素として処理しようとします。WHERE句のInnoDBテーブルの JSON カラムでMEMBER OF()を使用するクエリーは、複数値インデックスを使用して最適化できます。 詳細および例は、複数値インデックス を参照してください。単純なスカラーは、次に示すように配列値として扱われます:
mysql> SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');+-------------------------------------------+| 17 MEMBER OF('[23, "abc", 17, "ab", 10]') |+-------------------------------------------+| 1 |+-------------------------------------------+1 row in set (0.00 sec)mysql> SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');+---------------------------------------------+| 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]') |+---------------------------------------------+| 1 |+---------------------------------------------+1 row in set (0.00 sec)配列要素値の部分一致が一致しません:
mysql> SELECT 7 MEMBER OF('[23, "abc", 17, "ab", 10]');+------------------------------------------+| 7 MEMBER OF('[23, "abc", 17, "ab", 10]') |+------------------------------------------+| 0 |+------------------------------------------+1 row in set (0.00 sec)mysql> SELECT 'a' MEMBER OF('[23, "abc", 17, "ab", 10]');+--------------------------------------------+| 'a' MEMBER OF('[23, "abc", 17, "ab", 10]') |+--------------------------------------------+| 0 |+--------------------------------------------+1 row in set (0.00 sec)文字列型との間の変換は実行されません:
mysql> SELECT -> 17 MEMBER OF('[23, "abc", "17", "ab", 10]'), -> "17" MEMBER OF('[23, "abc", 17, "ab", 10]')\G*************************** 1. row ***************************17 MEMBER OF('[23, "abc", "17", "ab", 10]'): 0"17" MEMBER OF('[23, "abc", 17, "ab", 10]'): 01 row in set (0.00 sec)この演算子をそれ自体が配列である値とともに使用するには、JSON 配列として明示的にキャストする必要があります。 これは、
CAST(... AS JSON)で実行できます:mysql> SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]');+--------------------------------------------------+| CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]') |+--------------------------------------------------+| 1 |+--------------------------------------------------+1 row in set (0.00 sec)次のように、
JSON_ARRAY()関数を使用して必要なキャストを実行することもできます:mysql> SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]');+--------------------------------------------+| JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]') |+--------------------------------------------+| 1 |+--------------------------------------------+1 row in set (0.00 sec)テストする値として使用される JSON オブジェクト、またはターゲット配列に表示される JSON オブジェクトは、
CAST(... AS JSON)またはJSON_OBJECT()を使用して正しい型に強制変換する必要があります。 また、JSON オブジェクトを含むターゲット配列自体は、JSON_ARRAYを使用してキャストする必要があります。 これは、次の一連のステートメントで示されます:mysql> SET @a = CAST('{"a":1}' AS JSON);Query OK, 0 rows affected (0.00 sec)mysql> SET @b = JSON_OBJECT("b", 2);Query OK, 0 rows affected (0.00 sec)mysql> SET @c = JSON_ARRAY(17, @b, "abc", @a, 23);Query OK, 0 rows affected (0.00 sec)mysql> SELECT @a MEMBER OF(@c), @b MEMBER OF(@c);+------------------+------------------+| @a MEMBER OF(@c) | @b MEMBER OF(@c) |+------------------+------------------+| 1 | 1 |+------------------+------------------+1 row in set (0.00 sec)MEMBER OF()演算子が MySQL 8.0.17 に追加されました。