文档主页
MySQL 9.0 参考手册
相关文档 下载此手册
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
Man Pages (TGZ) - 258.2Kb
Man Pages (Zip) - 365.3Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 9.0 参考手册  /  ...  /  搜索 JSON 值的函数

14.17.3 搜索 JSON 值的函数

本节中的函数对 JSON 值执行搜索或比较操作,以从中提取数据,报告数据是否存在于其内的某个位置,或报告其内数据的路径。 MEMBER OF() 运算符也在此处进行说明。

  • JSON_CONTAINS(target, candidate[, path])

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

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

    以下规则定义了包含性

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

    • 如果且仅当候选者中的每个元素都包含在目标中的某个元素中,则候选数组包含在目标数组中。

    • 如果且仅当候选非数组包含在目标数组中的某个元素中,则候选非数组包含在目标数组中。

    • 如果且仅当候选者中的每个键在目标中都存在同名键,并且与候选者键关联的值包含在与目标键关联的值中,则候选对象包含在目标对象中。

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

    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] ...)

    返回 0 或 1 以指示 JSON 文档是否在给定路径或路径中包含数据。 如果任何参数为 NULL,则返回 NULL。 如果 json_doc 参数不是有效的 JSON 文档,任何 path 参数不是有效的路径表达式,或者 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 参数不是有效的路径表达式,则会发生错误。

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

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

    MySQL 支持 -> 运算符作为此函数的简写,该函数用于使用 2 个参数,其中左侧是 JSON 列标识符(而不是表达式),右侧是要在列中匹配的 JSON 路径。

  • column->path

    -> 运算符在使用两个参数时作为 JSON_EXTRACT() 函数的别名,左侧是列标识符,右侧是 JSON 路径(字符串文字),该路径针对 JSON 文档(列值)进行评估。 您可以在 SQL 语句中使用此类表达式代替列引用,无论它们出现在何处。

    此处所示的两个 SELECT 语句生成相同的输出

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

    此功能不仅限于 SELECT,如这里所示

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

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

    这同样适用于 JSON 数组值,如下所示

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

    支持嵌套数组。如果在目标 JSON 文档中未找到匹配的键,则使用 -> 的表达式将计算为 NULL,如下所示

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

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

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

    这是一个改进的、不加引号的提取运算符。虽然 -> 运算符只是提取一个值,但 ->> 运算符还会对提取的结果进行不加引号处理。换句话说,给定一个 JSON 列值 column 和一个路径表达式 path(一个字符串文字),以下三个表达式返回相同的值

    可以在允许使用 JSON_UNQUOTE(JSON_EXTRACT()) 的任何地方使用 ->> 运算符。这包括(但不限于)SELECT 列表、WHEREHAVING 子句以及 ORDER 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)

    有关用于创建和填充前面示例集中 jemp 表的 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 在相同情况下扩展 -> 运算符的方式类似。

  • JSON_KEYS(json_doc[, path])

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

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

    mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
    +---------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
    +---------------------------------------+
    | ["a", "b"]                            |
    +---------------------------------------+
    mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
    +----------------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
    +----------------------------------------------+
    | ["c"]                                        |
    +----------------------------------------------+
  • JSON_OVERLAPS(json_doc1, json_doc2)

    比较两个 JSON 文档。如果两个文档具有任何键值对或数组元素相同,则返回 true (1)。如果两个参数都是标量,则函数执行简单的相等性测试。如果任何参数为 NULL,则函数返回 NULL

    此函数充当 JSON_CONTAINS() 的对应函数,后者要求要搜索的数组的所有元素都存在于要搜索的数组中。因此,JSON_CONTAINS() 对搜索键执行 AND 操作,而 JSON_OVERLAPS() 执行 OR 操作。

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

    当比较两个数组时,如果它们共享一个或多个数组元素,则 JSON_OVERLAPS() 返回 true;如果它们不共享任何元素,则返回 false

    mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");
    +---------------------------------------+
    | JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]") |
    +---------------------------------------+
    |                                     1 |
    +---------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]");
    +---------------------------------------+
    | JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]") |
    +---------------------------------------+
    |                                     1 |
    +---------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");
    +---------------------------------------+
    | JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]") |
    +---------------------------------------+
    |                                     0 |
    +---------------------------------------+
    1 row in set (0.00 sec)

    部分匹配将被视为不匹配,如下所示

    mysql> SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');
    +-----------------------------------------------------+
    | JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]') |
    +-----------------------------------------------------+
    |                                                   0 |
    +-----------------------------------------------------+
    1 row in set (0.00 sec)

    当比较对象时,如果它们至少共享一个键值对,则结果为 true。

    mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');
    +-----------------------------------------------------------------------+
    | JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}') |
    +-----------------------------------------------------------------------+
    |                                                                     1 |
    +-----------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}');
    +-----------------------------------------------------------------------+
    | JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}') |
    +-----------------------------------------------------------------------+
    |                                                                     0 |
    +-----------------------------------------------------------------------+
    1 row in set (0.00 sec)

    如果使用两个标量作为函数的参数,JSON_OVERLAPS() 将执行简单的相等性测试

    mysql> SELECT JSON_OVERLAPS('5', '5');
    +-------------------------+
    | JSON_OVERLAPS('5', '5') |
    +-------------------------+
    |                       1 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT JSON_OVERLAPS('5', '6');
    +-------------------------+
    | JSON_OVERLAPS('5', '6') |
    +-------------------------+
    |                       0 |
    +-------------------------+
    1 row in set (0.00 sec)

    当比较标量与数组时,JSON_OVERLAPS() 尝试将标量视为数组元素。在本例中,第二个参数 6 被解释为 [6],如下所示

    mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '6');
    +---------------------------------+
    | JSON_OVERLAPS('[4,5,6,7]', '6') |
    +---------------------------------+
    |                               1 |
    +---------------------------------+
    1 row in set (0.00 sec)

    函数不执行类型转换

    mysql> SELECT JSON_OVERLAPS('[4,5,"6",7]', '6');
    +-----------------------------------+
    | JSON_OVERLAPS('[4,5,"6",7]', '6') |
    +-----------------------------------+
    |                                 0 |
    +-----------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"');
    +-----------------------------------+
    | JSON_OVERLAPS('[4,5,6,7]', '"6"') |
    +-----------------------------------+
    |                                 0 |
    +-----------------------------------+
    1 row in set (0.00 sec)
  • JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

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

    one_or_all 参数会影响搜索,如下所示

    • 'one': 搜索在第一次匹配后终止,并返回一个路径字符串。哪一个匹配被认为是第一个是未定义的。

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

    search_str 搜索字符串参数中,%_ 字符与 LIKE 运算符的作用相同:% 匹配任意数量的字符(包括零个字符),_ 匹配正好一个字符。

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

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

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

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

  • JSON_VALUE(json_doc, path)

    从指定文档中给定路径的 JSON 文档中提取值,并返回提取的值,可以选择将其转换为所需类型。完整的语法如下所示

    JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])
    
    on_empty:
        {NULL | ERROR | DEFAULT value} ON EMPTY
    
    on_error:
        {NULL | ERROR | DEFAULT value} ON ERROR

    json_doc 是一个有效的 JSON 文档。如果为 NULL,则函数返回 NULL

    path 是指向文档中位置的 JSON 路径。这必须是一个字符串文字值。

    type 是以下数据类型之一

    这些类型与 CAST() 函数支持的(非数组)类型相同。

    如果未通过 RETURNING 子句指定,则 JSON_VALUE() 函数的返回类型为 VARCHAR(512)。当未为返回类型指定字符集时,JSON_VALUE() 使用具有二进制排序规则的 utf8mb4,它是区分大小写的;如果将 utf8mb4 指定为结果的字符集,则服务器将使用此字符集的默认排序规则,它不区分大小写。

    当指定路径处的数据包含或解析为 JSON null 文字时,函数将返回 SQL NULL

    on_empty(如果指定)将确定 JSON_VALUE() 在未找到给定路径处的数据时的行为;此子句将采用以下值之一

    • NULL ON EMPTY: 函数返回 NULL;这是默认的 ON EMPTY 行为。

    • DEFAULT value ON EMPTY: 返回提供的 value。该值的类型必须与返回类型匹配。

    • ERROR ON EMPTY: 函数将引发错误。

    如果使用,on_error 将采用以下值之一,并具有相应的结果,如下所示

    • NULL ON ERROR: JSON_VALUE() 返回 NULL;如果未使用 ON ERROR 子句,这是默认行为。

    • DEFAULT value ON ERROR: 返回此值;其值必须与返回类型匹配。

    • ERROR ON ERROR: 将引发错误。

    如果使用,on_empty 必须位于任何 ON ERROR 子句之前。以错误的顺序指定它们会导致语法错误。

    错误处理。  通常,JSON_VALUE() 处理错误的方式如下

    • 将检查所有 JSON 输入(文档和路径)的有效性。如果任何输入无效,将引发 SQL 错误,而不会触发 ON ERROR 子句。

    • 当发生以下事件时,将触发 ON ERROR

      • 尝试提取对象或数组,例如由解析为 JSON 文档中多个位置的路径导致的对象或数组

      • 转换错误,例如尝试将 'asdf' 转换为 UNSIGNED

      • 值截断

    • 即使指定了 NULL ON ERRORDEFAULT ... ON ERROR,转换错误也会始终触发警告。

    • 当源 JSON 文档 (expr) 在指定位置 (path) 没有数据时,ON EMPTY 子句将被触发。

    示例。 这里显示了两个简单的示例

    mysql> SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
    +--------------------------------------------------------------+
    | JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') |
    +--------------------------------------------------------------+
    | Joe                                                          |
    +--------------------------------------------------------------+
    
    mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price'
        -> RETURNING DECIMAL(4,2)) AS price;
    +-------+
    | price |
    +-------+
    | 49.95 |
    +-------+

    语句 SELECT JSON_VALUE(json_doc, path RETURNING type) 等效于以下语句

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

    JSON_VALUE() 简化了在 JSON 列上创建索引,在许多情况下,它不需要创建生成的列,然后再在生成的列上创建索引。在创建具有 JSON 列的表 t1 时,可以通过在使用 JSON_VALUE() 操作该列的表达式(其路径与该列中的值匹配)上创建索引来实现此目的,如下所示

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

    以下 EXPLAIN 输出显示,针对 t1 的查询使用 WHERE 子句中的索引表达式使用了这样创建的索引

    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 列提供索引的生成列索引),如下所示

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

    针对此表的查询的 EXPLAIN 输出,引用生成列,显示索引的使用方式与之前针对表 t1 的查询相同

    mysql> EXPLAIN SELECT * FROM t2 WHERE g  = 123\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t2
       partitions: NULL
             type: ref
    possible_keys: i1
              key: i1
          key_len: 5
              ref: const
             rows: 1
         filtered: 100.00
            Extra: NULL

    有关在生成列上使用索引进行 JSON 列的间接索引的信息,请参阅 为 JSON 列提供索引的生成列索引

  • value MEMBER OF(json_array)

    如果 valuejson_array 的元素,则返回 true (1),否则返回 false (0)。value 必须是标量或 JSON 文档;如果是标量,则运算符尝试将其视为 JSON 数组的元素。如果 valuejson_arrayNULL,则函数返回 NULL

    WHERE 子句中对 InnoDB 表的 JSON 列使用 MEMBER OF() 的查询可以使用多值索引进行优化。有关详细信息和示例,请参阅 多值索引

    简单的标量被视为数组值,如下所示

    mysql> SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');
    +-------------------------------------------+
    | 17 MEMBER OF('[23, "abc", 17, "ab", 10]') |
    +-------------------------------------------+
    |                                         1 |
    +-------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');
    +---------------------------------------------+
    | 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]') |
    +---------------------------------------------+
    |                                           1 |
    +---------------------------------------------+
    1 row in set (0.00 sec)

    数组元素值的局部匹配不匹配

    mysql> SELECT 7 MEMBER OF('[23, "abc", 17, "ab", 10]');
    +------------------------------------------+
    | 7 MEMBER OF('[23, "abc", 17, "ab", 10]') |
    +------------------------------------------+
    |                                        0 |
    +------------------------------------------+
    1 row in set (0.00 sec)
    mysql> SELECT 'a' MEMBER OF('[23, "abc", 17, "ab", 10]');
    +--------------------------------------------+
    | 'a' MEMBER OF('[23, "abc", 17, "ab", 10]') |
    +--------------------------------------------+
    |                                          0 |
    +--------------------------------------------+
    1 row in set (0.00 sec)

    不会执行转换为字符串类型和从字符串类型转换

    mysql> SELECT
        -> 17 MEMBER OF('[23, "abc", "17", "ab", 10]'),
        -> "17" MEMBER OF('[23, "abc", 17, "ab", 10]')\G
    *************************** 1. row ***************************
    17 MEMBER OF('[23, "abc", "17", "ab", 10]'): 0
    "17" MEMBER OF('[23, "abc", 17, "ab", 10]'): 0
    1 row in set (0.00 sec)

    要将此运算符与本身为数组的值一起使用,需要将其显式地转换为 JSON 数组。可以使用 CAST(... AS JSON) 来实现此目的

    mysql> SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]');
    +--------------------------------------------------+
    | CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]') |
    +--------------------------------------------------+
    |                                                1 |
    +--------------------------------------------------+
    1 row in set (0.00 sec)

    也可以使用 JSON_ARRAY() 函数执行必要的转换,如下所示

    mysql> SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]');
    +--------------------------------------------+
    | JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]') |
    +--------------------------------------------+
    |                                          1 |
    +--------------------------------------------+
    1 row in set (0.00 sec)

    用作要测试的值或出现在目标数组中的任何 JSON 对象必须使用 CAST(... AS JSON)JSON_OBJECT() 转换为正确的类型。此外,包含 JSON 对象的目标数组本身必须使用 JSON_ARRAY 进行转换。以下语句序列演示了这一点

    mysql> SET @a = CAST('{"a":1}' AS JSON);
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SET @b = JSON_OBJECT("b", 2);
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SET @c = JSON_ARRAY(17, @b, "abc", @a, 23);
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @a MEMBER OF(@c), @b MEMBER OF(@c);
    +------------------+------------------+
    | @a MEMBER OF(@c) | @b MEMBER OF(@c) |
    +------------------+------------------+
    |                1 |                1 |
    +------------------+------------------+
    1 row in set (0.00 sec)