MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.18.3 JSON 値を検索する関数

MySQL 8.0 リファレンスマニュアル / ... / JSON 値を検索する関数

12.18.3 JSON 値を検索する関数

このセクションの関数では、JSON 値に対して検索または比較操作を実行して、JSON 値からデータを抽出したり、データが JSON 値内の場所に存在するかどうかをレポートしたり、JSON 値内のデータへのパスをレポートします。ここでは、MEMBER OF() 演算子についても説明します。

JSON_CONTAINS(target, candidate[, path])

特定の candidate JSON ドキュメントが target JSON ドキュメント内に含まれているかどうか、または 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: 0

mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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": "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: 0

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)

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: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select
json_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 [RETURNING type] [on_empty] [on_error])

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

on_error:
    {NULL | ERROR | DEFAULT value} ON ERROR
```
json_doc は有効な JSON ドキュメントです。

path は、ドキュメント内の場所を指す JSON パスです。

type は、次のいずれかのデータ型です:
- FLOAT
- DOUBLE
- DECIMAL
- SIGNED
- UNSIGNED
- DATE
- TIME
- DATETIME
- YEAR (MySQL 8.0.22 以降)
  
  1 桁または 2 桁の YEAR 値はサポートされていません。
- CHAR
- JSON
リストされている型は、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 value ON EMPTY: 指定された value が返されます。値の型は戻り値の型と一致する必要があります。
- ERROR ON EMPTY: この関数はエラーをスローします。
使用する場合、on_error は、次に示すように、エラーが発生したときに対応する結果とともに次のいずれかの値を取ります:
- NULL ON ERROR: JSON_VALUE() は NULL を返します。これは、ON ERROR 句が使用されていない場合のデフォルトの動作です。
- DEFAULT value ON ERROR: これは返される値です。その値は戻り型の値と一致する必要があります。
- ERROR 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, path RETURNING type) というステートメントは、次のステートメントと同等です:
```
SELECT CAST(
    JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) )
    AS type
);
```
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: ref
possible_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: ref
possible_keys: i1
          key: i1
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
```
生成されたカラムに対するインデックスを使用した JSON カラムの間接インデックス付けの詳細は、JSON カラムインデックスを提供するための生成されたカラムのインデックス付けを参照してください。

value MEMBER 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]'): 0
1 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 に追加されました。

関連キーワード: 　関数, all, SEARCH, パス, path, ドキュメント, row, OVERLAPS, 配列, カラム

PREV HOME UP NEXT