Chapter 12 Functions and Operators

12.18.3 Functions That Search JSON Values搜索JSON值的函数

The functions in this section perform search or comparison operations on JSON values to extract data from them, report whether data exists at a location within them, or report the path to data within them. 本节中的函数对JSON值执行搜索或比较操作，以从中提取数据，报告数据是否存在于其中的某个位置，或报告数据的路径。~~The MEMBER OF() operator is also documented herein.~~MEMBER OF()运算符也记录在这里。

JSON_CONTAINS(target, candidate[, path])

Indicates by returning 1 or 0 whether a given candidate JSON document is contained within a target JSON document, or—if a path argument was supplied—whether the candidate is found at a specific path within the target. 通过返回1或0指示给定的candidateJSON文档是否包含在目标JSON文档中，或者如果提供了路径参数，则指示该候选JSON文档是否位于目标中的特定路径。~~Returns NULL if any argument is NULL, or if the path argument does not identify a section of the target document.~~ 如果任何参数为NULL，或者路径参数未标识目标文档的节，则返回NULL。~~An error occurs if target or candidate is not a valid JSON document, or if the path argument is not a valid path expression or contains a * or ** wildcard.~~如果target或候选对象不是有效的JSON文档，或者path参数不是有效的路径表达式，或者包含*或**通配符，则会发生错误。

~~To check only whether any data exists at the path, use JSON_CONTAINS_PATH() instead.~~要仅检查路径中是否存在任何数据，请改用JSON_CONTAINS_PATH()。

~~The following rules define containment:~~以下规则定义包容：
- ~~A candidate scalar is contained in a target scalar if and only if they are comparable and are equal.~~ 当且仅当候选标量是可比较的且相等时，候选标量包含在目标标量中。~~Two scalar values are comparable if they have the same JSON_TYPE() types, with the exception that values of types INTEGER and DECIMAL are also comparable to each other.~~如果两个标量值具有相同的JSON_TYPE()类型，则它们具有可比性，但INTEGER和DECIMAL类型的值也具有可比性。
- ~~A candidate array is contained in a target array if and only if every element in the candidate is contained in some element of the target.~~当且仅当候选数组中的每个元素都包含在目标数组的某个元素中时，候选数组才包含在目标数组中。
- ~~A candidate nonarray is contained in a target array if and only if the candidate is contained in some element of the target.~~当且仅当候选数组包含在目标数组的某个元素中时，候选数组才包含在目标数组中。
- A candidate object is contained in a target object if and only if for each key in the candidate there is a key with the same name in the target and the value associated with the candidate key is contained in the value associated with the target key.当且仅当对于候选对象中的每个键，目标对象中存在具有相同名称的键，并且与候选键关联的值包含在与目标键关联的值中时，候选对象才包含在目标对象中。
~~Otherwise, the candidate value is not contained in the target document.~~否则，候选值不包含在目标文档中。

~~Starting with MySQL 8.0.17, queries using JSON_CONTAINS() on InnoDB tables can be optimized using multi-valued indexes; see Multi-Valued Indexes, for more information.~~从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] ...)

~~Returns 0 or 1 to indicate whether a JSON document contains data at a given path or paths.~~ 返回0或1以指示JSON文档是否包含给定路径中的数据。~~Returns NULL if any argument is NULL.~~ 如果任何参数为NULL，则返回NULL。~~An error occurs if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, or one_or_all is not 'one' or 'all'.~~如果json_doc参数不是有效的JSON文档，任一个path参数不是有效的路径表达式，或者one_or_all不是'one'或'all'，则会发生错误。

~~To check for a specific value at a path, use JSON_CONTAINS() instead.~~若要检查路径中的特定值，请改用JSON_CONTAINS()。

~~The return value is 0 if no specified path exists within the document.~~ 如果文档中不存在指定的路径，则返回值为0。~~Otherwise, the return value depends on the one_or_all argument:~~否则，返回值取决于one_or_all参数：
- 'one'~~: 1 if at least one path exists within the document, 0 otherwise.~~：如果文档中至少存在一个路径，则为1，否则为0。
- 'all'~~: 1 if all paths exist within the document, 0 otherwise.~~：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] ...)

~~Returns data from a JSON document, selected from the parts of the document matched by the path arguments.~~ 返回JSON文档中的数据，这些数据是从与path参数匹配的文档部分中选择的。~~Returns NULL if any argument is NULL or no paths locate a value in the document.~~ 如果任一个参数为NULL或文档中没有找到值，则返回NULL。~~An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression.~~如果json_doc参数不是有效的JSON文档或任一个path参数不是有效的路径表达式，则会发生错误。

~~The return value consists of all values matched by the path arguments.~~ 返回值由path参数匹配的所有值组成。~~If it is possible that those arguments could return multiple values, the matched values are autowrapped as an array, in the order corresponding to the paths that produced them.~~ 如果这些参数可能返回多个值，则匹配的值将自动包装为一个数组，顺序与生成它们的路径相对应。~~Otherwise, the return value is the single matched value.~~否则，返回值为单个匹配值。
```
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 supports the -> operator as shorthand for this function as used with 2 arguments where the left hand side is a JSON column identifier (not an expression) and the right hand side is the JSON path to be matched within the column.MySQL支持->运算符作为此函数的缩写，与2个参数一起使用，其中左侧是JSON列标识符（不是表达式），右侧是要在列中匹配的JSON路径。

column->path

The -> operator serves as an alias for the JSON_EXTRACT() function when used with two arguments, a column identifier on the left and a JSON path (a string literal) on the right that is evaluated against the JSON document (the column value). ->运算符与两个参数一起使用时充当JSON_EXTRACT()函数的别名，左边是列标识符，右边是根据JSON文档（列值）计算的JSON路径（字符串文本）。~~You can use such expressions in place of column references wherever they occur in SQL statements.~~在SQL语句中出现列引用的任何地方，都可以使用这些表达式来代替列引用。

~~The two SELECT statements shown here produce the same output:~~此处显示的两个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)

~~This functionality is not limited to SELECT, as shown here:~~此功能不限于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)

~~(See Indexing a Generated Column to Provide a JSON Column Index, for the statements used to create and populate the table just shown.)~~（有关用于创建和填充刚刚显示的表的语句，请参阅索引生成的列以提供JSON列索引。）

~~This also works with JSON array values, as shown here:~~这也适用于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)

~~Nested arrays are supported.~~ 支持嵌套数组。~~An expression using -> evaluates as NULL if no matching key is found in the target JSON document, as shown here:~~表达式使用->如果在目标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)

~~This is the same behavior as seen in such cases when using JSON_EXTRACT():~~这与使用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

~~This is an improved, unquoting extraction operator.~~ 这是一个改进的，不加引号的提取操作符。~~Whereas the -> operator simply extracts a value, the ->> operator in addition unquotes the extracted result.~~ ->运算符只提取一个值，此外，->>运算符还对提取的结果去引号。~~In other words, given a JSON column value column and a path expression path (a string literal), the following three expressions return the same value:~~换句话说，给定一个JSON列值column和一个路径表达式path（字符串文本），以下三个表达式返回相同的值：

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

~~The ->> operator can be used wherever JSON_UNQUOTE(JSON_EXTRACT()) would be allowed.~~ ->>运算符可以在任何允许使用JSON_UNQUOTE(JSON_EXTRACT())的地方使用。~~This includes (but is not limited to) SELECT lists, WHERE and HAVING clauses, and ORDER BY and GROUP BY clauses.~~这包括（但不限于）SELECT列表、WHERE子句和HAVING子句以及ORDER BY子句和GROUP BY子句。

~~The next few statements demonstrate some ->> operator equivalences with other expressions in the mysql client:~~接下来的几句话展示了一些->>运算符，与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)

~~See Indexing a Generated Column to Provide a JSON Column Index, for the SQL statements used to create and populate the jemp table in the set of examples just shown.~~请参阅索引生成的列以提供JSON列索引，以获取刚才所示示例集中用于创建和填充jemp表的SQL语句。

~~This operator can also be used with JSON arrays, as shown here:~~此运算符也可用于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)

~~As with ->, the ->> operator is always expanded in the output of EXPLAIN, as the following example demonstrates:~~与->一样；->>运算符总是在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)

~~This is similar to how MySQL expands the -> operator in the same circumstances.~~这类似于在同样的情况下MySQL如何扩展->。

JSON_KEYS(json_doc[, path])

~~Returns the keys from the top-level value of a JSON object as a JSON array, or, if a path argument is given, the top-level keys from the selected path.~~ 以JSON数组的形式返回JSON对象的顶级值中的键，如果给定了path参数，则返回所选路径中的顶级键。~~Returns NULL if any argument is NULL, the json_doc argument is not an object, or path, if given, does not locate an object.~~ 如果任何参数为NULL、json_doc参数不是对象，或者path（如果给定）未定位对象，则返回NULL。~~An error occurs if the json_doc argument is not a valid JSON document or the path argument is not a valid path expression or contains a * or ** wildcard.~~如果json_doc参数不是有效的JSON文档，或者path参数不是有效的路径表达式，或者包含*或**通配符，则会发生错误。

~~The result array is empty if the selected object is empty.~~ 如果选定对象为空，则结果数组为空。~~If the top-level value has nested subobjects, the return value does not include keys from those subobjects.~~如果顶级值具有嵌套的子对象，则返回值不包括来自这些子对象的键。
```
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)

~~Compares two JSON documents.~~ 比较两个JSON文档。~~Returns true (1) if the two document have any key-value pairs or array elements in common.~~ 如果两个文档有任何共同的键值对或数组元素，则返回true（1）。~~If both arguments are scalars, the function performs a simple equality test.~~如果两个参数都是标量，则函数将执行简单的相等性测试。

~~This function serves as counterpart to JSON_CONTAINS(), which requires all elements of the array searched for to be present in the array searched in.~~ 此函数相当于JSON_CONTAINS()，它要求所搜索的数组中的所有元素都存在于所搜索的数组中。~~Thus, JSON_CONTAINS() performs an AND operation on search keys, while JSON_OVERLAPS() performs an OR operation.~~因此，JSON_CONTAINS()对搜索键执行AND操作，而JSON_OVERLAPS()执行OR操作。

~~Queries on JSON columns of InnoDB tables using JSON_OVERLAPS() in the WHERE clause can be optimized using multi-valued indexes.~~ 在WHERE子句中使用JSON_OVERLAPS()对InnoDB表的JSON列的查询可以使用多值索引进行优化。~~Multi-Valued Indexes, provides detailed information and examples.~~多值索引，提供详细信息和示例。

~~When comparing two arrays, JSON_OVERLAPS() returns true if they share one or more array elements in common, and false if they do not:~~比较两个数组时，如果它们共用一个或多个数组元素，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)

~~Partial matches are treated as no match, as shown here:~~部分匹配被视为不匹配，如下所示：

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)

~~When comparing objects, the result is true if they have at least one key-value pair in common.~~比较对象时，如果它们至少有一个共同的键值对，则结果为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)

~~If two scalars are used as the arguments to the function, JSON_OVERLAPS() performs a simple test for equality:~~如果将两个标量用作函数的参数，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)

~~When comparing a scalar with an array, JSON_OVERLAPS() attempts to treat the scalar as an array element.~~ 在比较标量和数组时，JSON_OVERLAPS()尝试将标量视为数组元素。~~In this example, the second argument 6 is interpreted as [6], as shown here:~~在本例中，第二个参数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)

~~The function does not perform type conversions:~~函数不执行类型转换：

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() was added in MySQL 8.0.17.~~JSON_OVERLAPS()是在MySQL 8.0.17中新增的。

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

~~Returns the path to the given string within a JSON document.~~ 返回JSON文档中给定字符串的路径。~~Returns NULL if any of the json_doc, search_str, or path arguments are NULL; no path exists within the document; or search_str is not found.~~ 如果任何json_doc、search_str或path参数为NULL，或文档中不存在path；或找不到search_str，则返回NULL。An error occurs if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, one_or_all is not 'one' or 'all', or escape_char is not a constant expression.如果json_doc参数不是有效的JSON文档，任一个路径参数不是有效的路径表达式，或one_or_all不是'one'或'all'，或者escape_char不是常量表达式，则会发生错误。

~~The one_or_all argument affects the search as follows:~~one_or_all参数影响搜索，如下所示：

'one'~~: The search terminates after the first match and returns one path string.~~ ：搜索在第一次匹配后终止，并返回一个路径字符串。~~It is undefined which match is considered first.~~不确定哪一个匹配被认为是第一个。
'all'~~: The search returns all matching path strings such that no duplicate paths are included.~~ ：搜索返回所有匹配的路径字符串，以便不包括重复的路径。~~If there are multiple strings, they are autowrapped as an array.~~ 如果有多个字符串，它们将自动包装为数组。~~The order of the array elements is undefined.~~数组元素的顺序未定义。

Within the search_str search string argument, the % and _ characters work as for the LIKE operator: % matches any number of characters (including zero characters), and _ matches exactly one character.在search_str搜索字符串参数中，%和_字符的工作方式与LIKE运算符中的这两个字符的工作方式相同：%匹配任意数量的字符（包括零个字符），而_只匹配一个字符。

~~To specify a literal % or _ character in the search string, precede it by the escape character.~~ 若要在搜索字符串中指定文字%或_字符，请在其前面加上转义字符。~~The default is \ if the escape_char argument is missing or NULL.~~ 如果escape_char参数丢失或为空，则默认值为\。~~Otherwise, escape_char must be a constant that is empty or one character.~~否则，escape_char必须是空字符或一个字符的常量。

~~For more information about matching and escape character behavior, see the description of LIKE in Section 12.8.1, “String Comparison Functions and Operators”.~~ 有关匹配和转义字符行为的更多信息，请参阅第12.8.1节，“字符串比较函数和运算符”中的LIKE说明。~~For escape character handling, a difference from the LIKE behavior is that the escape character for JSON_SEARCH() must evaluate to a constant at compile time, not just at execution time.~~ 对于转义字符处理，与同类行为的区别在于，JSON_SEARCH()的转义字符必须在编译时计算为常量，而不仅仅是在执行时。For example, if JSON_SEARCH() is used in a prepared statement and the escape_char argument is supplied using a ? parameter, the parameter value might be constant at execution time, but is not at compile time.例如，如果在准备好的语句中使用了JSON_SEARCH()，而escape_char参数是使用?参数，参数值在执行时可能是常量，但在编译时不是。

search_str and path are always interpeted as utf8mb4 strings, regardless of their actual encoding. search_str和path总是被解释为utf8mb4字符串，而不管它们的实际编码是什么。~~This is a known issue which is fixed in MySQL 8.0.24 ( Bug #32449181).~~这是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"                                  |
+-------------------------------------------+

~~For more information about the JSON path syntax supported by MySQL, including rules governing the wildcard operators * and **, see JSON Path Syntax.~~有关MySQL支持的JSON路径语法的更多信息，包括管理通配符运算符*和**的规则，请参阅JSON路径语法。

JSON_VALUE(json_doc, path)

~~Extracts a value from a JSON document at the path given in the specified document, and returns the extracted value, optionally converting it to a desired type.~~ 按照指定文档中给定的路径从JSON文档中提取值，并返回提取的值，还可以选择将其转换为所需的类型。~~The complete syntax is shown here:~~完整的语法如下所示：
```
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 ~~is a valid JSON document.~~是有效的JSON文档。

path ~~is a JSON path pointing to a location in the document.~~ 是指向文档中某个位置的JSON路径。~~This must be a string literal value.~~这必须是字符串文字值。

type ~~is one of the following data types:~~是以下数据类型之一：
- FLOAT
- DOUBLE
- DECIMAL
- SIGNED
- UNSIGNED
- DATE
- TIME
- DATETIME
- YEAR ~~(MySQL 8.0.22 and later)~~（MySQL 8.0.22及更高版本）
  
  YEAR ~~values of one or two digits are not supported.~~值，不支持一位或两位数字。
- CHAR
- JSON
~~The types just listed are the same as the (non-array) types supported by the CAST() function.~~刚才列出的类型与CAST()函数支持的（非数组）类型相同。

~~If not specified by a RETURNING clause, the JSON_VALUE() function's return type is VARCHAR(512).~~ 如果不是由RETURNING子句指定，那么JSON_VALUE()函数的返回类型是VARCHAR(512)。When no character set is specified for the return type, JSON_VALUE() uses utf8mb4 with the binary collation, which is case-sensitive; if utf8mb4 is specified as the character set for the result, the server uses the default collation for this character set, which is not case-sensitive.当没有为返回类型指定字符集时，JSON_VALUE()使用带二进制排序规则的utf8mb4，这是区分大小写的；如果将utf8mb4指定为结果的字符集，则服务器将使用此字符集的默认排序规则，该排序规则不区分大小写。

~~When the data at the specified path consists of or resolves to a JSON null literal, the function returns SQL NULL.~~当指定路径上的数据包含或解析为JSONnull文本时，函数返回SQLNULL。

on_empty, if specified, determines how JSON_VALUE() behaves when no data is found at the path given; this clause takes one of the following values:on_empty，如果指定，则确定在给定的路径上找不到数据时JSON_VALUE()的行为；此子句采用以下值之一：
- NULL ON EMPTY~~: The function returns NULL; this is the default ON EMPTY behavior.~~：函数返回NULL；这是空行为的默认值。
- DEFAULT value ON EMPTY~~: the provided value is returned.~~ ：返回提供的value。~~The value's type must match that of the return type.~~值的类型必须与返回类型的类型匹配。
- ERROR ON EMPTY~~: The function throws an error.~~：函数抛出错误。
~~If used, on_error takes one of the following values with the corresponding outcome when an error occurs, as listed here:~~如果使用，当发生错误时，on_error将采用以下值之一以及相应的结果，如下所示：
- NULL ON ERROR~~: JSON_VALUE() returns NULL; this is the default behavior if no ON ERROR clause is used.~~：JSON_VALUE()返回NULL；如果不使用ON ERROR子句，这是默认行为。
- DEFAULT value ON ERROR~~: This is the value returned; its value must match that of the return type.~~：返回值；其值必须与返回类型的值匹配。
- ERROR ON ERROR~~: An error is thrown.~~：抛出错误。
~~ON EMPTY, if used, must precede any ON ERROR clause.~~ ON EMPTY（如果使用）必须用在任何ON ERROR子句之前。~~Specifying them in the wrong order results in a syntax error.~~以错误的顺序指定它们会导致语法错误。

~~Error handling.~~错误处理。 ~~In general, errors are handled by JSON_VALUE() as follows:~~通常，错误由JSON_VALUE()处理，如下所示：
- ~~All JSON input (document and path) is checked for validity.~~ 检查所有JSON输入（文档和路径）的有效性。~~If any of it is not valid, an SQL error is thrown without triggering the ON ERROR clause.~~如果其中任何一个无效，则抛出SQL错误而不触发ON ERROR子句。
- ~~ON ERROR is triggered whenever any of the following events occur:~~只要发生以下任何事件，就会触发ON ERROR：
  - ~~Attempting to extract an object or an array, such as that resulting from a path that resolves to multiple locations within the JSON document~~试图提取一个对象或数组，例如解析到JSON文档中多个位置的路径所产生的对象或数组
  - ~~Conversion errors, such as attempting to convert 'asdf' to an UNSIGNED value~~转换错误，例如试图将'asdf'转换为UNSIGNED值
  - ~~Truncation of values~~值的截断
- ~~A conversion error always triggers a warning even if NULL ON ERROR or DEFAULT ... ON ERROR is specified.~~转换错误总是会触发警告，即使指定了NULL ON ERROR或DEFAULT ... ON ERROR。
- ~~The ON EMPTY clause is triggered when the source JSON document (expr) contains no data at the specified location (path).~~当源JSON文档（expr）在指定位置（路径）不包含任何数据时，将触发ON EMPTY子句。
~~JSON_VALUE() was introduced in MySQL 8.0.21.~~JSON_VALUE()是在MySQL 8.0.21中新增的。

~~Examples.~~示例。 ~~Two simple examples are shown here:~~以下是两个简单的例子：
```
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 |
+-------+
```
~~The statement SELECT JSON_VALUE(json_doc, path RETURNING type) is equivalent to the following statement:~~语句SELECT JSON_VALUE(json_doc, path RETURNING type)等同于以下语句：
```
SELECT CAST(
    JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) )
    AS type
);
```
~~JSON_VALUE() simplifies creating indexes on JSON columns by making it unnecessary in many cases to create a generated column and then an index on the generated column.~~ JSON_VALUE()简化了在JSON列上创建索引的过程，因为在许多情况下不需要先创建生成的列，然后再在生成的列上创建索引。You can do this when creating a table t1 that has a JSON column by creating an index on an expression that uses JSON_VALUE() operating on that column (with a path that matches a value in that column), as shown here:在创建具有JSON列的表t1时，可以通过在表达式上创建索引来执行此操作，该表达式使用对该列进行操作的JSON_VALUE()（路径与该列中的值匹配），如下所示：
```
CREATE TABLE t1(
    j JSON,
    INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
);
```
~~The following EXPLAIN output shows that a query against t1 employing the index expression in the WHERE clause uses the index thus created:~~下面的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
```
~~This achieves much the same effect as creating a table t2 with an index on a generated column (see Indexing a Generated Column to Provide a JSON Column Index), like this one:~~这与在生成的列上创建带有索引的表t2（请参阅为生成的列编制索引以提供JSON列索引）的效果大致相同，如下所示：
```
CREATE TABLE t2 (
    j JSON,
    g INT GENERATED ALWAYS AS (j->"$.id"),
    INDEX i1 (j)
);
```
~~The EXPLAIN output for a query against this table, referencing the generated column, shows that the index is used in the same way as for the previous query against table t1:~~针对该表的查询的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
```
~~For information about using indexes on generated columns for indirect indexing of JSON columns, see Indexing a Generated Column to Provide a JSON Column Index.~~有关在生成的列上使用索引来间接索引JSON列的信息，请参阅索引生成的列以提供JSON列索引。

value MEMBER OF(json_array)

~~Returns true (1) if value is an element of json_array, otherwise returns false (0).~~ 如果value是json_array的元素，则返回true（1），否则返回false（0）。value must be a scalar or a JSON document; if it is a scalar, the operator attempts to treat it as an element of a JSON array.值必须是标量或JSON文档；如果它是一个标量，则操作符会尝试将其视为JSON数组的元素。

~~Queries using MEMBER OF() on JSON columns of InnoDB tables in the WHERE clause can be optimized using multi-valued indexes.~~ 可以使用多值索引优化WHERE子句中InnoDB表的JSON列上使用MEMBER OF()的查询。~~See Multi-Valued Indexes, for detailed information and examples.~~有关详细信息和示例，请参见多值索引。

~~Simple scalars are treated as array values, as shown here:~~简单标量被视为数组值，如下所示：

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)

~~Partial matches of array element values do not match:~~数组元素值的部分匹配不匹配：

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)

~~Conversions to and from string types are not performed:~~不执行与字符串类型之间的转换：

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)

~~To use this operator with a value which itself an array, it is necessary to cast it explicitly as a JSON array.~~ 要将此运算符与本身是数组的值一起使用，必须将其显式转换为JSON数组。~~You can do this with CAST(... AS 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)

~~It is also possible to perform the necessary cast using the JSON_ARRAY() function, like this:~~也可以使用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)

~~Any JSON objects used as values to be tested or which appear in the target array must be coerced to the correct type using CAST(... AS JSON) or JSON_OBJECT().~~ 任何用作要测试的值或出现在目标数组中的JSON对象都必须使用CAST(... AS JSON)或JSON_OBJECT()。~~In addition, a target array containing JSON objects must itself be cast using JSON_ARRAY.~~ 此外，包含JSON对象的目标数组本身必须使用JSON_ARRAY强制转换。~~This is demonstrated in the following sequence of statements:~~这一点可以通过以下陈述顺序来说明：

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)

~~The MEMBER OF() operator was added in MySQL 8.0.17.~~MEMBER OF()运算符是在MySQL 8.0.17中新增的。