MySQL :: MySQL 8.4 Reference Manual :: 14.17.3 Functions That Search JSON Values

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Functions That Search JSON Values

14.17.3 JSON 值搜索函数

本节中的函数执行 JSON 值的搜索或比较操作，以从中提取数据、报告位于其中的数据是否存在或报告其中的数据路径。还包括了MEMBER OF()运算符的文档。

JSON_CONTAINS(目标, 候选人[, 路径])

如果给定的候选人 JSON 文档包含在目标 JSON 文档中，或者—if a 路径 argument was supplied—whether the candidate is found at a specific path within the target，返回 1 或 0。返回NULL如果任何参数为NULL，或如果路径参数不识别目标文档的部分。如果目标或候选人不是有效的 JSON 文档，或者路径参数不是有效的路径表达式或包含*或**通配符，则发生错误。

要检查是否存在于路径中的任何数据，可以使用JSON_CONTAINS_PATH()代替。

以下规则定义了包含关系：
- 候选标量包含在目标标量中，如果和仅当它们是可比较的且相等。两个标量值是可比较的，如果它们具有相同的JSON_ TYPE()类型，除非值类型为INTEGER和DECIMAL也可以相互比较。
- 候选数组包含在目标数组中，如果和仅当候选数组中的每个元素都包含在目标数组中的某个元素中。
- 候选非数组包含在目标数组中，如果候选非数组包含在目标数组中的某个元素中。
- 候选对象包含在目标对象中，如果对于候选对象中的每个键，目标对象中存在具有相同名称的键，并且候选键关联的值包含在目标键关联的值中。
否则，候选值不包含在目标文档中。

使用InnoDB表中的JSON_ CONTAINS()查询可以使用多值索引优化；见Multi-Valued Indexes，了解更多信息。
```
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 文档是否包含给定路径或路径的数据的布尔值。返回 NULL 如果任何参数为 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者任何 path 参数不是有效的路径表达式，则发生错误。

要检查特定值在路径上是否存在，可以使用 JSON_CONTAINS() 而不是。

返回值为 0，如果指定的路径在文档中不存在。否则，返回值取决于 one_或_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

->操作符在使用 2 个参数时，左侧是列标识符，右侧是 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 列索引，了解用于创建和填充上面所示表的语句。)

这同样适用于 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(一个字符串字面量)，以下三个表达式返回相同的值：

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

->> 运算符可以在任何允许使用 JSON_UNQUOTE(JSON_EXTRACT()) 的地方使用。这包括（但不限于）SELECT 列表、WHERE 和 HAVING take、ORDER BY 和 GROUP BY take。

以下几个语句演示了->> 运算符与其他表达式在 <strong>mysql</strong> 客户端的等价关系：

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)

请参阅Indexing a Generated Column to Provide a JSON Column Index，了解创建和填充<code>jemp</code> 表的 SQL 语句。

这个运算符也可以与 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 文档。返回 true（1）如果两个文档具有任何键-值对或数组元素在公共。如果两个参数都是标量，则函数执行简单的等价测试。如果任何一个参数为NULL，则函数返回NULL。

这个函数是JSON_CONTAINS()的对应函数，它需要搜索数组中的所有元素都在被搜索的数组中。因此，JSON_CONTAINS()执行的是AND操作，而JSON_OVERLAPS()执行的是OR操作。

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

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

当比较对象时，结果是true，如果它们至少有一个键值对在common。

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_SEARCH(json_文档, one_or_all, 搜索字符串[, .escape 字符[, 路径] ...])

返回 JSON 文档中给定字符串的路径。如果任何json_文档、搜索字符串或路径参数为NULL，或者在文档中找不到search_字符串，则返回NULL。如果json_文档参数不是有效的 JSON 文档、任何路径参数不是有效的路径表达式、one_or_all不是'one'或'all'，或者escape 字符不是常量表达式，则发生错误。

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

'one': 搜索在找到第一个匹配项后终止，并返回一个路径字符串。哪个匹配项被认为是第一个是未定义的。
'all': 搜索返回所有匹配路径字符串，且不包括重复路径。如果有多个字符串，它们将自动包围成数组。数组元素的顺序是未定义的。

在搜索字符串参数 search_str 中，% 和 _ 字符与 LIKE 操作符的工作方式相同：% 匹配任意数量的字符（包括零个字符），_ 匹配恰好一个字符。

要在搜索字符串中指定一个字面量 % 或 _ 字符，请将其前缀为转义字符。默认情况下，如果缺少或为空的 escape_char 参数，则使用 \ 作为转义字符。否则，escape_char 必须是一个空字符串或一个字符。

关于匹配和转义字符行为的更多信息，请见LIKE在第14.8.1节，“字符串比较函数和操作符”的描述。关于转义字符处理，JSON_SEARCH()与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 Path Syntax。

JSON_VALUE(json_doc, path)

从 JSON 文档中提取值，根据指定的文档路径，并返回提取的值， optionally 将其转换为所需类型。完整语法如下：
```
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
  
  单个或双个数字的 YEAR 值不受支持。
- CHAR
- JSON
上述类型与CAST()函数支持的非数组类型相同。

如果没有指定RETURNING子句，JSON_VALUE()函数的返回类型为VARCHAR(512)。如果没有指定返回类型的字符集，JSON_VALUE()使用utf8mb4字符集，具有二进制排序规则，这是区分大小写的；如果指定了utf8mb4字符集，则服务器将使用该字符集的默认排序规则，该规则不是区分大小写。

当指定路径中的数据或解析到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 输入（文档和路径）都将被检查是否有效。如果其中任何一部分无效，将抛出 SQL 错误，而不会触发ON ERROR子句。
- ON ERROR 在以下事件发生时被触发：
  - 尝试提取对象或数组，例如来自 JSON 文档的路径 resolve 到多个位置
  - 转换错误，例如将'asdf'转换为UNSIGNED值
  - 值截断
- 总是会在指定了NULL ON ERROR或DEFAULT ... ON ERROR时抛出警告。
- ON EMPTY子句在源 JSON 文档(expr)中指定位置(path)无数据时被触发。
示例. 以下展示了两个简单的示例：
```
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_文档, 路径 RETURNING 类型) 等同于以下语句：
```
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 输出显示，使用在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
```
这实现了与创建一个表t2，具有一个生成列索引（见Indexing a Generated Column to Provide a JSON Column Index）的相同效果，如下所示：
```
CREATE TABLE t2 (
    j JSON,
    g INT GENERATED ALWAYS AS (j->"$.id"),
    INDEX i1 (g)
);
```
对该表的查询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 数组的一个元素。如果value或json_array是NULL，则函数返回NULL。

使用InnoDB表中的 JSON 列在 WHERE 子句中使用 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 对象都必须使用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)

PREV HOME UP NEXT