Documentation Home

12.18.3 搜索 JSON 值的函数

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

  • JSON_CONTAINS(target, candidate[, path])

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

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

    以下规则定义了遏制:

    • 当且仅当它们可比较且相等时,候选标量才包含在目标标量中。如果两个标量值具有相同的 JSON_TYPE()类型,则它们是可比较的,但类型INTEGERDECIMAL的值也可以相互比较。

    • 当且仅当候选中的每个元素都包含在目标的某个元素中时,候选数组才包含在目标数组中。

    • 当且仅当候选者包含在目标的某个元素中时,候选非数组才包含在目标数组中。

    • 候选对象包含在目标对象中,当且仅当对于候选对象中的每个键,目标对象中都有一个同名的键,并且与候选键关联的值包含在与目标键关联的值中。

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

    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 如果任何参数是,则返回NULLjson_doc如果参数不是有效的 JSON 文档,任何path 参数都不是有效的路径表达式,或者 one_or_all不是 'one'or ,则会发生错误'all'

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

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

    • 'one':如果文档中至少存在一个路径,则为 1,否则为 0。

    • 'all':如果文档中存在所有路径,则为 1,否则为 0。

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

    从 JSON 文档返回数据,这些数据是从文档中与path 参数匹配的部分中选择的。返回NULL是否有任何参数是 NULL或没有路径在文档中找到一个值。json_doc如果参数不是有效的 JSON 文档或任何path参数不是有效的路径表达式, 则会发生错误 。

    返回值由 path参数匹配的所有值组成。如果这些参数可能返回多个值,则匹配的值将自动包装为一个数组,顺序与生成它们的路径相对应。否则,返回值为单个匹配值。

    mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
    +--------------------------------------------+
    | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
    +--------------------------------------------+
    | 20                                         |
    +--------------------------------------------+
    mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
    +----------------------------------------------------+
    | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
    +----------------------------------------------------+
    | [20, 10]                                           |
    +----------------------------------------------------+
    mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
    +-----------------------------------------------+
    | JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
    +-----------------------------------------------+
    | [30, 40]                                      |
    +-----------------------------------------------+

    MySQL 5.7.9 及更高版本支持 -> 运算符作为此函数的简写形式,与 2 个参数一起使用,其中左侧是 JSON列标识符(不是表达式),右侧是要在列中匹配的 JSON 路径。

  • column->path

    在 MySQL 5.7.9 及更高版本中, -> 运算符在与两个参数一起使用时充当函数的别名, JSON_EXTRACT()左侧是列标识符,右侧是根据 JSON 文档(列值)。您可以在 SQL 语句中出现的任何地方使用此类表达式来代替列引用。

    此处显示的两个SELECT语句产生相同的输出:

    mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
         > FROM jemp
         > WHERE JSON_EXTRACT(c, "$.id") > 1
         > ORDER BY JSON_EXTRACT(c, "$.name");
    +-------------------------------+-----------+------+
    | c                             | c->"$.id" | g    |
    +-------------------------------+-----------+------+
    | {"id": "3", "name": "Barney"} | "3"       |    3 |
    | {"id": "4", "name": "Betty"}  | "4"       |    4 |
    | {"id": "2", "name": "Wilma"}  | "2"       |    2 |
    +-------------------------------+-----------+------+
    3 rows in set (0.00 sec)
    
    mysql> SELECT c, c->"$.id", g
         > FROM jemp
         > WHERE c->"$.id" > 1
         > ORDER BY c->"$.name";
    +-------------------------------+-----------+------+
    | c                             | c->"$.id" | g    |
    +-------------------------------+-----------+------+
    | {"id": "3", "name": "Barney"} | "3"       |    3 |
    | {"id": "4", "name": "Betty"}  | "4"       |    4 |
    | {"id": "2", "name": "Wilma"}  | "2"       |    2 |
    +-------------------------------+-----------+------+
    3 rows in set (0.00 sec)

    此功能不限于 SELECT,如下所示:

    mysql> ALTER TABLE jemp ADD COLUMN n INT;
    Query OK, 0 rows affected (0.68 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4";
    Query OK, 1 row affected (0.04 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT c, c->"$.id", g, n
         > FROM jemp
         > WHERE JSON_EXTRACT(c, "$.id") > 1
         > ORDER BY c->"$.name";
    +-------------------------------+-----------+------+------+
    | c                             | c->"$.id" | g    | n    |
    +-------------------------------+-----------+------+------+
    | {"id": "3", "name": "Barney"} | "3"       |    3 | NULL |
    | {"id": "4", "name": "Betty"}  | "4"       |    4 |    1 |
    | {"id": "2", "name": "Wilma"}  | "2"       |    2 | NULL |
    +-------------------------------+-----------+------+------+
    3 rows in set (0.00 sec)
    
    mysql> DELETE FROM jemp WHERE c->"$.id" = "4";
    Query OK, 1 row affected (0.04 sec)
    
    mysql> SELECT c, c->"$.id", g, n
         > FROM jemp
         > WHERE JSON_EXTRACT(c, "$.id") > 1
         > ORDER BY c->"$.name";
    +-------------------------------+-----------+------+------+
    | c                             | c->"$.id" | g    | n    |
    +-------------------------------+-----------+------+------+
    | {"id": "3", "name": "Barney"} | "3"       |    3 | NULL |
    | {"id": "2", "name": "Wilma"}  | "2"       |    2 | NULL |
    +-------------------------------+-----------+------+------+
    2 rows in set (0.00 sec)

    (有关用于创建和填充刚才显示的表的语句, 请参阅索引生成的列以提供 JSON 列索引。)

    这也适用于 JSON 数组值,如下所示:

    mysql> CREATE TABLE tj10 (a JSON, b INT);
    Query OK, 0 rows affected (0.26 sec)
    
    mysql> INSERT INTO tj10
         > VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);
    Query OK, 1 row affected (0.04 sec)
    
    mysql> SELECT a->"$[4]" FROM tj10;
    +--------------+
    | a->"$[4]"    |
    +--------------+
    | 44           |
    | [22, 44, 66] |
    +--------------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT * FROM tj10 WHERE a->"$[0]" = 3;
    +------------------------------+------+
    | a                            | b    |
    +------------------------------+------+
    | [3, 10, 5, 17, 44]           |   33 |
    | [3, 10, 5, 17, [22, 44, 66]] |    0 |
    +------------------------------+------+
    2 rows in set (0.00 sec)

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

    mysql> SELECT * FROM tj10 WHERE a->"$[4][1]" IS NOT NULL;
    +------------------------------+------+
    | a                            | b    |
    +------------------------------+------+
    | [3, 10, 5, 17, [22, 44, 66]] |    0 |
    +------------------------------+------+
    
    mysql> SELECT a->"$[4][1]" FROM tj10;
    +--------------+
    | a->"$[4][1]" |
    +--------------+
    | NULL         |
    | 44           |
    +--------------+
    2 rows in set (0.00 sec)

    这与使用时在这种情况下看到的行为相同 JSON_EXTRACT()

    mysql> SELECT JSON_EXTRACT(a, "$[4][1]") FROM tj10;
    +----------------------------+
    | JSON_EXTRACT(a, "$[4][1]") |
    +----------------------------+
    | NULL                       |
    | 44                         |
    +----------------------------+
    2 rows in set (0.00 sec)
  • column->>path

    这是 MySQL 5.7.13 及更高版本中可用的改进的、不引用的提取运算符。->运算符只是提取一个值,而 运算 ->>符还取消引用提取的结果。换句话说,给定一个 JSON列值 column和一个路径表达式 path(字符串文字),以下三个表达式返回相同的值:

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

    接下来的几个语句演示了一些 运算符与mysql->>客户端中其他表达式的等效性:

    mysql> SELECT * FROM jemp WHERE g > 2;
    +-------------------------------+------+
    | c                             | g    |
    +-------------------------------+------+
    | {"id": "3", "name": "Barney"} |    3 |
    | {"id": "4", "name": "Betty"}  |    4 |
    +-------------------------------+------+
    2 rows in set (0.01 sec)
    
    mysql> SELECT c->'$.name' AS name
        ->     FROM jemp WHERE g > 2;
    +----------+
    | name     |
    +----------+
    | "Barney" |
    | "Betty"  |
    +----------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
        ->     FROM jemp WHERE g > 2;
    +--------+
    | name   |
    +--------+
    | Barney |
    | Betty  |
    +--------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT c->>'$.name' AS name
        ->     FROM jemp WHERE g > 2;
    +--------+
    | name   |
    +--------+
    | Barney |
    | Betty  |
    +--------+
    2 rows in set (0.00 sec)

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

    此运算符也可用于 JSON 数组,如下所示:

    mysql> CREATE TABLE tj10 (a JSON, b INT);
    Query OK, 0 rows affected (0.26 sec)
    
    mysql> INSERT INTO tj10 VALUES
        ->     ('[3,10,5,"x",44]', 33),
        ->     ('[3,10,5,17,[22,"y",66]]', 0);
    Query OK, 2 rows affected (0.04 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> SELECT a->"$[3]", a->"$[4][1]" FROM tj10;
    +-----------+--------------+
    | a->"$[3]" | a->"$[4][1]" |
    +-----------+--------------+
    | "x"       | NULL         |
    | 17        | "y"          |
    +-----------+--------------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT a->>"$[3]", a->>"$[4][1]" FROM tj10;
    +------------+---------------+
    | a->>"$[3]" | a->>"$[4][1]" |
    +------------+---------------+
    | x          | NULL          |
    | 17         | y             |
    +------------+---------------+
    2 rows in set (0.00 sec)

    与 一样 ->->>运算符始终在 的输出中展开EXPLAIN,如以下示例所示:

    mysql> EXPLAIN SELECT c->>'$.name' AS name
        ->     FROM jemp WHERE g > 2\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: jemp
       partitions: NULL
             type: range
    possible_keys: i
              key: i
          key_len: 5
              ref: NULL
             rows: 2
         filtered: 100.00
            Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS\G
    *************************** 1. row ***************************
      Level: Note
       Code: 1003
    Message: /* select#1 */ select
    json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from
    `jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2)
    1 row in set (0.00 sec)

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

    ->>运算符是在 MySQL 5.7.13 中添加的。

  • JSON_KEYS(json_doc[, path])

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

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

    mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
    +---------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
    +---------------------------------------+
    | ["a", "b"]                            |
    +---------------------------------------+
    mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
    +----------------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
    +----------------------------------------------+
    | ["c"]                                        |
    +----------------------------------------------+
  • JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

    返回 JSON 文档中给定字符串的路径。如果, , 或 参数中 NULL的任何一个是 ,则返回;文档中不 存在;或未 找到。如果参数不是有效的 JSON 文档、任何 参数都不是有效的路径表达式、 不是 or或 不是常量表达式,则会发生错误。 json_docsearch_strpathNULLpathsearch_strjson_docpathone_or_all'one''all'escape_char

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

    • 'one':搜索在第一次匹配后终止并返回一个路径字符串。未定义首先考虑哪个匹配。

    • 'all':搜索返回所有匹配的路径字符串,不包括重复路径。如果有多个字符串,它们将自动包装为一个数组。数组元素的顺序未定义。

    search_str搜索字符串参数中,%_ 字符作为LIKE 运算符工作:%匹配任意数量的字符(包括零个字符),并 _恰好匹配一个字符。

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

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

    search_str并且 path总是被插入为 utf8mb4 字符串,无论它们的实际编码如何。这是一个已知问题,已在 MySQL 8.0 中修复(Bug #32449181)。

    mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
    
    mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
    +-------------------------------+
    | JSON_SEARCH(@j, 'one', 'abc') |
    +-------------------------------+
    | "$[0]"                        |
    +-------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
    +-------------------------------+
    | JSON_SEARCH(@j, 'all', 'abc') |
    +-------------------------------+
    | ["$[0]", "$[2].x"]            |
    +-------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
    +-------------------------------+
    | JSON_SEARCH(@j, 'all', 'ghi') |
    +-------------------------------+
    | NULL                          |
    +-------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10');
    +------------------------------+
    | JSON_SEARCH(@j, 'all', '10') |
    +------------------------------+
    | "$[1][0].k"                  |
    +------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
    +-----------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$') |
    +-----------------------------------------+
    | "$[1][0].k"                             |
    +-----------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
    +--------------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
    +--------------------------------------------+
    | "$[1][0].k"                                |
    +--------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
    +---------------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
    +---------------------------------------------+
    | "$[1][0].k"                                 |
    +---------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
    +-------------------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
    +-------------------------------------------------+
    | "$[1][0].k"                                     |
    +-------------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
    +--------------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
    +--------------------------------------------+
    | "$[1][0].k"                                |
    +--------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
    +-----------------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
    +-----------------------------------------------+
    | "$[1][0].k"                                   |
    +-----------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
    +---------------------------------------------+
    | JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
    +---------------------------------------------+
    | "$[2].x"                                    |
    +---------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
    +-------------------------------+
    | JSON_SEARCH(@j, 'all', '%a%') |
    +-------------------------------+
    | ["$[0]", "$[2].x"]            |
    +-------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
    +-------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%') |
    +-------------------------------+
    | ["$[0]", "$[2].x", "$[3].y"]  |
    +-------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
    +---------------------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
    +---------------------------------------------+
    | "$[0]"                                      |
    +---------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
    +---------------------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
    +---------------------------------------------+
    | "$[2].x"                                    |
    +---------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
    +---------------------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
    +---------------------------------------------+
    | NULL                                        |
    +---------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
    +-------------------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
    +-------------------------------------------+
    | NULL                                      |
    +-------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
    +-------------------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
    +-------------------------------------------+
    | "$[3].y"                                  |
    +-------------------------------------------+

    有关 MySQL 支持的 JSON 路径语法的更多信息,包括管理通配符运算符 *和的规则**,请参阅 JSON 路径语法