12.17.3 搜索 JSON 值的函数

本部分中的函数对 JSON 值执行搜索操作，以从其中提取数据，报告数据是否在其中的某个位置或报告其中的数据的路径。

JSON_CONTAINS(target, candidate[, path])

通过返回 1 或 0 来指示给定的* candidate * JSON 文档是否包含在* target * JSON 文档中，或者(如果提供了* path 参数)table 示是否在目标内的特定路径上找到了候选对象。如果任何参数为NULL，或者 path 参数不能标识目标文档的一部分，则返回NULL。如果 target 或 candidate 不是有效的 JSON 文档，或者 path *参数不是有效的路径 table 达式或包含*或**通配符，则会发生错误。

要仅检查路径中是否存在任何数据，请改用JSON_CONTAINS_PATH()。

以下规则定义了围堵：

当且仅当候选标量可比较且相等时才包含在目标标量中。如果两个标量值具有相同的JSON_TYPE()类型，则它们是可比较的，但类型INTEGER和DECIMAL的值也可以彼此比较。
- 当且仅当候选对象中的每个元素都包含在目标的某个元素中时，候选数组才包含在目标数组中。
- 当且仅当候选非数组包含在目标的某个元素中时，该候选非数组才包含在目标数组中。
- 当且仅当候选对象中的每个关键字在目标中存在一个具有相同名称的关键字并且与候选关键字相关联的值包含在与目标关键字相关联的值中时，候选对象才包含在目标对象中。

否则，候选值将不包含在目标文档中。

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参数不是有效的路径 table 达式或* one_or_all *不是'one'或'all'，则会发生错误。

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

如果文档中没有指定的路径，则返回值为 0.否则，返回值取决于* one_or_all *参数：

'one'：如果文档中至少存在一个路径，则为 1，否则为 0.
- 'all'：如果文档中所有路径都存在，则为 1，否则为 0.

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+

JSON_EXTRACT(json_doc，path [，path] ...)

从 JSON 文档中返回数据，该数据是从与* path 参数匹配的文档部分中选择的。如果任何参数为NULL或没有路径在文档中找到值，则返回NULL。如果 json_doc 参数不是有效的 JSON 文档，或者任何 path *参数不是有效的路径 table 达式，则会发生错误。

返回值包含所有与* path *参数匹配的值。如果这些参数有可能返回多个值，则将匹配的值按照与产生它们的路径相对应的 Sequences 自动包装为一个数组。否则，返回值是单个匹配值。

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 5.7.9 和更高版本支持->运算符作为此函数的简写形式，与 2 个参数一起使用，其中左侧是JSON列标识符(不是 table 达式)，右侧是要在列内匹配的 JSON 路径。

column->path

在 MySQL 5.7.9 和更高版本中，与两个参数一起使用时，->运算符用作JSON_EXTRACT()函数的别名，两个参数是左侧的列标识符，右侧的 JSON 路径是根据 JSON 文档评估的(列值) 。您可以在 SQL 语句中的任何位置使用此类 table 达式代替列标识符。

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

(有关用于创建和填充刚刚显示的 table 的语句，请参见索引生成的列以提供 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 文档中未找到匹配关键字，则使用->的 table 达式的值为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

这是 MySQL 5.7.13 和更高版本中提供的一种改进的，取消引用的提取运算符。 ->运算符只是提取一个值，而->>运算符还取消了提取结果的引用。换句话说，给定JSON列值* column 和路径 table 达式 path *，以下三个 table 达式将返回相同的值：

JSON_UNQUOTE( JSON_EXTRACT(列，路径))
- JSON_UNQUOTE(column -> path)
- column->>path

可以在允许JSON_UNQUOTE(JSON_EXTRACT())的任何地方使用->>运算符。这包括(但不限于)SELECT列 table，WHERE和HAVING子句以及ORDER BY和GROUP BY子句。

接下来的几条语句说明了mysqlClient 端中的其他 table 达式与->>运算符的等效关系：

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)

有关刚才创建的示例集中用于创建和填充jemptable 的 SQL 语句，请参见索引生成的列以提供 JSON 列索引。

此运算符也可以与 JSON 数组一起使用，如下所示：

mysql> CREATE TABLE tj10 (a JSON, b INT);
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO tj10 VALUES
    ->     ('[3,10,5,"x",44]', 33),
    ->     ('[3,10,5,17,[22,"y",66]]', 0);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a->"$[3]", a->"$[4][1]" FROM tj10;
+-----------+--------------+
| a->"$[3]" | a->"$[4][1]" |
+-----------+--------------+
| "x"       | NULL         |
| 17        | "y"          |
+-----------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT a->>"$[3]", a->>"$[4][1]" FROM tj10;
+------------+---------------+
| a->>"$[3]" | a->>"$[4][1]" |
+------------+---------------+
| x          | NULL          |
| 17         | y             |
+------------+---------------+
2 rows in set (0.00 sec)

与->一样，->>运算符总是在EXPLAIN的输出中扩展，如以下示例所示：

mysql> EXPLAIN SELECT c->>'$.name' AS name
    ->     FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select
json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from
`jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

这类似于在相同情况下 MySQL 扩展->运算符的方式。

MySQL 5.7.13 中添加了->>运算符。

JSON_KEYS(json_doc[, path])

从 JSON 对象的顶级值作为 JSON 数组返回键，或者，如果给出* path 参数，则从所选路径返回顶级键。如果任何参数是NULL，则返回NULL， json_doc 参数不是对象，或者 path (如果给出的话)没有找到对象。如果 json_doc参数不是有效的 JSON 文档，或者* path *参数不是有效的路径 table 达式或包含*或**通配符，则会发生错误。

如果所选对象为空，则结果数组为空。如果顶级值具有嵌套的子对象，则返回值不包括来自那些子对象的键。

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

返回 JSON 文档中给定字符串的路径。如果* json_doc ， search_str 或 path 参数中的任何一个为NULL，则返回NULL；文档中不存在 path ；或 search_str 未找到。如果 json_doc 参数不是有效的 JSON 文档，任何 path 参数不是有效的路径 table 达式， one_or_all 不是'one'或'all'，或者 escape_char *不是常量 table 达式，则会发生错误。

one_or_all *参数会影响搜索，如下所示：

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

在* search_str *搜索字符串参数中，%和_字符与LIKE运算符相同：%匹配任意数量的字符(包括零个字符)，而_恰好匹配一个字符。

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

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

mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]"                        |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'ghi') |
+-------------------------------+
| NULL                          |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10');
+------------------------------+
| JSON_SEARCH(@j, 'all', '10') |
+------------------------------+
| "$[1][0].k"                  |
+------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
+-----------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$') |
+-----------------------------------------+
| "$[1][0].k"                             |
+-----------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
+---------------------------------------------+
| "$[1][0].k"                                 |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
+-------------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
+-------------------------------------------------+
| "$[1][0].k"                                     |
+-------------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
+-----------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
+-----------------------------------------------+
| "$[1][0].k"                                   |
+-----------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%a%') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%b%') |
+-------------------------------+
| ["$[0]", "$[2].x", "$[3].y"]  |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
+---------------------------------------------+
| "$[0]"                                      |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
+-------------------------------------------+
| "$[3].y"                                  |
+-------------------------------------------+

有关 MySQL 支持的 JSON 路径语法的更多信息，包括 Management 通配符*和**的规则，请参见JSON 路径语法。

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35