JSON_CONTAINS(target, candidate[, path])

通过返回 1 或 0 来指示给定的 candidate JSON 文档是否包含在 target JSON 文档中，或者——如果提供了 path 参数——候选是否在目标文档的特定路径中找到。如果任何参数为 NULL，或者路径参数未标识目标文档的某个部分，则返回 NULL。如果 target 或 candidate 不是有效的 JSON 文档，或者 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. Returns NULL if any argument is NULL, or if the path argument does not identify a section of the target document. 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.

如果只想检查路径上是否存在任何数据，请改用 JSON_CONTAINS_PATH()。To check only whether any data exists at the path, use JSON_CONTAINS_PATH() instead.

以下规则定义了包含关系：The following rules define containment:

否则，候选值不包含在目标文档中。Otherwise, the candidate value is not contained in the target document.

在 InnoDB 表上使用 JSON_CONTAINS() 的查询可以通过多值索引进行优化；有关详细信息，请参阅 多值索引。Queries using JSON_CONTAINS() on InnoDB tables can be optimized using multi-valued indexes; see Multi-Valued Indexes, for more information.

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' 或 'all'，则会发生错误。Returns 0 or 1 to indicate whether a JSON document contains data at a given path or paths. Returns NULL if any argument is 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_CONTAINS()。To check for a specific value at a path, use JSON_CONTAINS() instead.

如果文档中不存在指定路径，则返回值为 0。否则，返回值取决于 one_or_all 参数：The return value is 0 if no specified path exists within the document. Otherwise, the return value depends on the one_or_all argument:

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 参数不是有效的路径表达式，则会发生错误。Returns data from a JSON document, selected from the parts of the document matched by the path arguments. Returns NULL if any argument is NULL or no paths locate a value in the document. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression.

返回值包含所有由 path 参数匹配的值。如果这些参数可能返回多个值，则匹配的值会自动封装为数组，顺序与生成它们的路径对应。否则，返回值为单个匹配值。The return value consists of all values matched by the path arguments. 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 支持 -> 操作符，作为此函数的简写形式，当使用两个参数时，左侧是 JSON 列标识符（不是表达式），右侧是在列中要匹配的 JSON 路径。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.

column->path

-> 操作符在与两个参数一起使用时，相当于 JSON_EXTRACT() 函数，其中左边是列标识符，右边是针对 JSON 文档（列值）评估的 JSON 路径（字符串字面量）。你可以在 SQL 语句中出现列引用的任何地方使用这样的表达式。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). You can use such expressions in place of column references wherever they occur in SQL statements.

下面显示的两个 SELECT 语句会产生相同的输出：The two SELECT statements shown here produce the same output:

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，如下面所示：This functionality is not limited to SELECT, as shown here:

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 列索引，了解用于创建和填充上面显示的表的语句。）(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:

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，如下所示：Nested arrays are supported. An expression using -> evaluates as NULL if no matching key is found in the target JSON document, as shown here:

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() 时，也会出现相同的行为：This is the same behavior as seen in such cases when using 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（字符串字面量），以下三个表达式返回相同的值：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_UNQUOTE(JSON_EXTRACT()) 被允许的任何地方使用。这包括（但不限于）SELECT 列表、WHERE 和 HAVING 子句，以及 ORDER BY 和 GROUP BY 子句。The ->> operator can be used wherever JSON_UNQUOTE(JSON_EXTRACT()) would be allowed. This includes (but is not limited to) SELECT lists, WHERE and HAVING clauses, and ORDER BY and GROUP BY clauses.

接下来的几条语句演示了一些 ->> 运算符与 mysql 客户端中其他表达式的等价关系：The next few statements demonstrate some ->> operator equivalences with other expressions in the mysql client:

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)

请参阅 为 JSON 列提供索引的生成列索引，了解用于创建和填充之前示例中 jemp 表的 SQL 语句。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 数组，如下所示：This operator can also be used with JSON arrays, as shown here:

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 输出中总是被展开，正如以下示例所示：As with ->, the ->> operator is always expanded in the output of EXPLAIN, as the following example demonstrates:

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 在相同情况下展开 -> 操作符的方式。This is similar to how MySQL expands the -> operator in the same circumstances.

JSON_KEYS(json_doc[, path])

返回 JSON 对象顶级值的键作为 JSON 数组，或者如果提供了 path 参数，则返回所选路径的顶级键。如果任何参数为 NULL，json_doc 参数不是对象，或者提供的 path 并未定位到对象，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者 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. Returns NULL if any argument is NULL, the json_doc argument is not an object, or path, if given, does not locate an object. 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.

如果所选对象为空，则结果数组为空。如果顶层值有嵌套的子对象，则返回值不包括这些子对象的键。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)

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

此函数作为 JSON_CONTAINS() 的对应函数，后者要求搜索的数组中的所有元素都必须存在于被搜索的数组中。因此，JSON_CONTAINS() 对搜索键执行 AND 操作，而 JSON_OVERLAPS() 执行 OR 操作。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. Thus, JSON_CONTAINS() performs an AND operation on search keys, while JSON_OVERLAPS() performs an OR operation.

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

在比较两个数组时，如果它们有一个或多个数组元素相同，JSON_OVERLAPS() 返回 true；如果没有，则返回 false：When comparing two arrays, JSON_OVERLAPS() returns true if they share one or more array elements in common, and false if they do not:

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.

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() 会执行一个简单的相等性测试：If two scalars are used as the arguments to the function, JSON_OVERLAPS() performs a simple test for equality:

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]，如下所示：When comparing a scalar with an array, JSON_OVERLAPS() attempts to treat the scalar as an array element. In this example, the second argument 6 is interpreted as [6], as shown here:

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_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

返回给定字符串在 JSON 文档中的路径。如果 json_doc、search_str 或 path 参数为 NULL；文档中不存在任何 path；或者未找到 search_str，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，任何 path 参数不是有效的路径表达式，one_or_all 不是 'one' 或 'all'，或 escape_char 不是常量表达式，则会发生错误。Returns the path to the given string within a JSON document. 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. 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.

one_or_all 参数对搜索的影响如下：The one_or_all argument affects the search as follows:

在 search_str 搜索字符串参数中，% 和 _ 字符的作用与 LIKE 运算符相同：% 匹配任意数量的字符（包括零个字符），而 _ 精确匹配一个字符。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.

若要在搜索字符串中指定字面量的 % 或 _ 字符，请在其前加上转义字符。如果 escape_char 参数缺失或为 NULL，默认转义字符为 \。否则，escape_char 必须是一个空字符或单个字符的常量。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. Otherwise, escape_char must be a constant that is empty or one character.

有关匹配和转义字符行为的更多信息，请参阅 LIKE 在 第 14.8.1 节，“字符串比较函数和操作符” 中的描述。关于转义字符的处理，与 LIKE 行为的区别在于，JSON_SEARCH() 的转义字符必须在编译时求值为常量，而不仅仅是在执行时。例如，如果在预处理语句中使用 JSON_SEARCH()，并且 escape_char 参数是通过 ? 参数提供的，则该参数值在执行时可能是常量，但在编译时不是常量。For more information about matching and escape character behavior, see the description of LIKE in Section 14.8.1, “String Comparison Functions and Operators”. 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. 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.

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 路径语法。For more information about the JSON path syntax supported by MySQL, including rules governing the wildcard operators * and **, see JSON Path Syntax.

JSON_VALUE(json_doc, path)

从指定文档中给定路径的 JSON 文档中提取一个值，并返回提取的值，可选择将其转换为所需的类型。完整的语法如下所示：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. 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 是一个有效的 JSON 文档。如果它是 NULL，函数将返回 NULL。json_doc is a valid JSON document. If this is NULL, the function returns NULL.

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

type 是以下数据类型之一：type is one of the following data types:

刚列出的类型与 CAST() 函数支持的（非数组）类型相同。The types just listed are the same as the (non-array) types supported by the CAST() function.

如果没有通过 RETURNING 子句指定，JSON_VALUE() 函数的返回类型为 VARCHAR(512)。当返回类型未指定字符集时，JSON_VALUE() 使用二进制排序规则的 utf8mb4，区分大小写；如果结果指定了 utf8mb4 作为字符集，服务器将使用该字符集的默认排序规则，该排序规则不区分大小写。If not specified by a RETURNING clause, the JSON_VALUE() function's return type is 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 null 字面量时，函数返回 SQL NULL。When the data at the specified path consists of or resolves to a JSON null literal, the function returns SQL NULL.

on_empty，如果指定，会决定在给定路径未找到数据时 JSON_VALUE() 的行为；该子句可取下列值之一：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_error 在发生错误时可取下列值之一，相应的结果如上所列：If used, on_error takes one of the following values with the corresponding outcome when an error occurs, as listed here:

ON EMPTY 子句（如果使用）必须位于任何 ON ERROR 子句之前。顺序错误会导致语法错误。ON EMPTY, if used, must precede any ON ERROR clause. Specifying them in the wrong order results in a syntax error.

错误处理。 一般情况下，JSON_VALUE() 按如下方式处理错误：Error handling.  In general, errors are handled by JSON_VALUE() as follows:

示例。 这里显示两个简单的示例：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 |
+-------+

除非 JSON_VALUE() 返回 NULL，否则语句 SELECT JSON_VALUE(json_doc, path RETURNING type) 等价于以下语句：Except in cases where JSON_VALUE() returns NULL, the statement SELECT JSON_VALUE(json_doc, path RETURNING type) is equivalent to the following statement:

SELECT CAST(
    JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) )
    AS type
);

JSON_VALUE() 通过在许多情况下无需创建生成列然后在生成列上创建索引，从而简化了在 JSON 列上创建索引的过程。当创建包含 JSON 列的表 t1 时，可以通过在使用该列的 JSON_VALUE()（其路径与该列中的值匹配）的表达式上创建索引来实现，如下所示：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. 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:

CREATE TABLE t1(
    j JSON,
    INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
);

下面的 EXPLAIN 输出显示，对 t1 的查询在 WHERE 子句中使用索引表达式时，使用了由此创建的索引：The following EXPLAIN output shows that a query against t1 employing the index expression in the WHERE clause uses the index thus created:

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 列索引），如下所示：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:

CREATE TABLE t2 (
    j JSON,
    g INT GENERATED ALWAYS AS (j->"$.id"),
    INDEX i1 (g)
);

针对该表的查询并引用生成列的 EXPLAIN 输出显示，该索引的使用方式与之前对表 t1 的查询相同：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:

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 列提供索引的生成列索引。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.

value MEMBER OF(json_array)

如果 value 是 json_array 的元素，则返回 true (1)，否则返回 false (0)。value 必须是标量或 JSON 文档；如果它是标量，运算符会尝试将其作为 JSON 数组的一个元素处理。如果 value 或 json_array 为 NULL，函数将返回 NULL。Returns true (1) if value is an element of json_array, otherwise returns 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. If value or json_array is NULL, the function returns NULL.

在 WHERE 子句中，对 InnoDB 表的 JSON 列使用 MEMBER OF() 查询，可以使用多值索引进行优化。有关详细信息和示例，请参见 多值索引。Queries using MEMBER OF() on JSON columns of InnoDB tables in the WHERE clause can be optimized using multi-valued indexes. 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)

要将这个运算符用于本身就是数组的值，必须显式地将其转换为 JSON 数组。您可以使用 CAST(... AS JSON) 来完成此操作：To use this operator with a value which is itself an array, it is necessary to cast it explicitly as a JSON array. You can do this with 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() 函数执行所需的转换，方法如下：It is also possible to perform the necessary cast using the JSON_ARRAY() function, like this:

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 进行转换。在以下语句序列中演示了这一点：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(). In addition, a target array containing JSON objects must itself be cast using 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)