12.18.3 搜索 JSON 值的函数_MySQL 8.0 参考手册

MySQL 8.0 参考手册 / 第 12 章函数和运算符 / 12.17空间分析函数 / 12.17.11 空间 GeoJSON 函数 / 12.18.3 搜索 JSON 值的函数

12.18.3 搜索 JSON 值的函数

本节中的函数对 JSON 值执行搜索或比较操作以从中提取数据，报告数据是否存在于其中的某个位置，或报告其中数据的路径。MEMBER OF() 运算符也记录在本文中。

JSON_CONTAINS(target, candidate[, path])
通过返回 1 或 0 来指示给定的 candidateJSON 文档是否包含在targetJSON 文档中，或者 - 如果path 提供了参数 - 是否在目标内的特定路径中找到了候选项。NULL如果任何参数是 NULL，或者路径参数未标识目标文档的一部分，则返回。target如果或 candidate不是有效的 JSON 文档，或者path参数不是有效的路径表达式或包含 *或**通配符，则会发生错误。
要仅检查路径中是否存在任何数据，请 JSON_CONTAINS_PATH()改用。
以下规则定义了遏制：
- 当且仅当它们可比较且相等时，候选标量才包含在目标标量中。如果两个标量值具有相同的 JSON_TYPE()类型，则它们是可比较的，但类型INTEGER 和DECIMAL的值也可以相互比较。
- 当且仅当候选中的每个元素都包含在目标的某个元素中时，候选数组才包含在目标数组中。
- 当且仅当候选者包含在目标的某个元素中时，候选非数组才包含在目标数组中。
- 候选对象包含在目标对象中，当且仅当对于候选对象中的每个键，目标对象中都有一个同名的键，并且与候选键关联的值包含在与目标键关联的值中。
否则，候选值不包含在目标文档中。
从 MySQL 8.0.17 开始，使用表的查询 JSON_CONTAINS()可以 InnoDB使用多值索引进行优化；有关详细信息，请参阅多值索引。
```
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] ...)

返回 0 或 1 以指示 JSON 文档是否包含给定路径或路径中的数据。NULL 如果任何参数是，则返回NULL。json_doc如果参数不是有效的 JSON 文档，任何path 参数都不是有效的路径表达式，或者 one_or_all不是 'one'or ，则会发生错误'all'。

要检查路径中的特定值，请 JSON_CONTAINS()改用。

如果文档中不存在指定路径，则返回值为 0。否则，返回值取决于 one_or_all参数：

'one'：如果文档中至少存在一个路径，则为 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] ...)

从 JSON 文档返回数据，这些数据是从文档中与path 参数匹配的部分中选择的。返回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 支持将 -> 运算符作为此函数的简写形式，与 2 个参数一起使用，其中左侧是 JSON列标识符（不是表达式），右侧是要在列中匹配的 JSON 路径。

column->path

当与两个参数一起使用时，-> 运算符用作 JSON_EXTRACT()函数的别名，左侧是列标识符，右侧是根据 JSON 文档（列值）评估的 JSON 路径（字符串文字）。您可以在 SQL 语句中出现的任何地方使用此类表达式来代替列引用。

此处显示的两个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)

支持嵌套数组。表达式 using 的 ->计算结果就NULL 好像在目标 JSON 文档中找不到匹配的键一样，如下所示：

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（字符串文字），以下三个表达式返回相同的值：

JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path

->>可以在任何 JSON_UNQUOTE(JSON_EXTRACT())允许的地方使用运算符。这包括（但不限于） SELECT列表、条款和WHERE条款。 HAVINGORDER BYGROUP 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)

有关用于在刚刚显示的示例集中创建和填充表的 SQL 语句，请参阅索引生成的列以提供 JSON 列索引。jemp

此运算符也可用于 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如果任何参数是，则返回NULL，该 json_doc参数不是对象，或者path，如果给定，则找不到对象。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)

比较两个 JSON 文档。如果两个文档有任何共同的键值对或数组元素，则返回真 (1)。如果两个参数都是标量，则该函数执行简单的相等性测试。如果任一参数是NULL，则函数返回NULL。

此函数与对应 JSON_CONTAINS()，它要求搜索到的数组的所有元素都存在于搜索到的数组中。因此，对搜索键执行操作，同时JSON_CONTAINS() 执行操作。 ANDJSON_OVERLAPS()OR

可以使用多值索引优化在子句中InnoDB 使用的对表的 JSON 列的查询。多值索引，提供详细信息和示例。 JSON_OVERLAPS()WHERE

比较两个数组时，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)

比较对象时，如果它们至少有一个共同的键值对，则结果为真。

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)

如果两个标量用作函数的参数，则 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()尝试将标量视为数组元素。在此示例中，第二个参数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 文档中给定字符串的路径。如果, , 或参数中 NULL的任何一个是，则返回；文档中不存在；或未找到。如果参数不是有效的 JSON 文档、任何参数都不是有效的路径表达式、不是 or或不是常量表达式，则会发生错误。 json_docsearch_strpathNULLpathsearch_strjson_docpathone_or_all'one''all'escape_char

该one_or_all参数对搜索的影响如下：

'one'：搜索在第一次匹配后终止并返回一个路径字符串。未定义首先考虑哪个匹配。
'all'：搜索返回所有匹配的路径字符串，不包括重复路径。如果有多个字符串，它们将自动包装为一个数组。数组元素的顺序未定义。

在search_str搜索字符串参数中，%和_ 字符作为LIKE 运算符工作：%匹配任意数量的字符（包括零个字符），并 _恰好匹配一个字符。

要在搜索字符串中指定文字%或 _字符，请在其前面加上转义字符。默认值是 \参数 escape_char丢失或 NULL. 否则， escape_char必须是一个空常量或一个字符。

有关匹配和转义字符行为的更多信息，请参阅第 12.8.1 节，“字符串比较函数和运算符”LIKE中的描述。对于转义字符处理，与行为的不同之处在于转义字符 for 必须在编译时评估为常量，而不仅仅是在执行时。例如，如果在准备好的语句中使用并且参数是使用参数提供的，则参数值在执行时可能是常量，但在编译时不是。 LIKEJSON_SEARCH()JSON_SEARCH()escape_char?

search_str并且 path总是被解释为 utf8mb4 字符串，无论它们的实际编码如何。这是一个已知问题，已在 MySQL 8.0.24 中修复（Bug #32449181）。

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 文档。如果是NULL，函数返回 NULL。
path是指向文档中某个位置的 JSON 路径。这必须是字符串文字值。
type是以下数据类型之一：
- FLOAT
- DOUBLE
- DECIMAL
- SIGNED
- UNSIGNED
- DATE
- TIME
- DATETIME
- YEAR（MySQL 8.0.22 及更高版本）
  YEAR不支持一位或两位数字的值。
- CHAR
- JSON
CAST() 刚刚列出的类型与函数支持的（非数组）类型相同。
RETURNING如果子句未指定，则JSON_VALUE()函数的返回类型为 VARCHAR(512). 当没有为返回类型指定字符集时， JSON_VALUE()使用 utf8mb4with二进制排序规则，区分大小写；如果utf8mb4指定为结果的字符集，则服务器使用此字符集的默认排序规则，不区分大小写。
当指定路径中的数据由 JSON 空文字组成或解析为 JSON 空文字时，该函数返回 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 ERRORor ，转换错误也总是会触发警告DEFAULT ... ON ERROR。
- 当源 JSON 文档 ( ) 在指定位置 ( ) 不包含任何数据时，ON EMPTY将触发该子句。 exprpath
JSON_VALUE()在 MySQL 8.0.21 中引入。
例子。 这里显示了两个简单的例子：
```
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 列上创建索引。您可以在创建t1 包含一JSON列的表时执行此操作，方法是在对该列使用操作的表达式上创建索引 JSON_VALUE()（具有与该列中的值匹配的路径），如下所示：
```
CREATE TABLE t1(
    j JSON,
    INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
);
```
以下EXPLAIN输出显示针对在子句中使用 t1索引表达式的查询使用了这样创建的索引：WHERE
```
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
```
这实现了与在生成列上创建具有索引的表大致相同的效果 t2（请参阅为生成的列编制索引以提供 JSON 列索引），如下所示：
```
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如果是的元素则返回真 (1) json_array，否则返回假 (0)。value必须是标量或 JSON 文档；如果它是标量，则运算符会尝试将其视为 JSON 数组的元素。如果 value或 json_array是 NULL，函数返回 NULL。

可以使用多值索引优化子句中使用表的 MEMBER OF()JSON 列的查询。有关详细信息和示例，请参阅多值索引。InnoDBWHERE

简单标量被视为数组值，如下所示：

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 对象都必须使用 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 中添加的。