mysql json 函数

12.17.1 JSON函数参考

表12.21 JSON函数

Name 描述
-> 评估路径后从JSON列返回值;等效于JSON_EXTRACT()。
->> (介绍5.7.13) 评估路径并取消引用结果后,从JSON列返回值;等效于JSON_UNQUOTE(JSON_EXTRACT())。
JSON_APPEND() (已弃用) 将数据附加到JSON文档
JSON_ARRAY() 创建JSON数组
JSON_ARRAY_APPEND() 将数据附加到JSON文档
JSON_ARRAY_INSERT() 插入JSON数组
JSON_CONTAINS() JSON文档是否在路径中包含特定对象
JSON_CONTAINS_PATH() JSON文档是否在路径中包含任何数据
JSON_DEPTH() JSON文档的最大深度
JSON_EXTRACT() 从JSON文档返回数据
JSON_INSERT() 将数据插入JSON文档
JSON_KEYS() JSON文档中的键数组
JSON_LENGTH() JSON文档中的元素数
JSON_MERGE() (不建议使用5.7.22) 合并JSON文档,保留重复的键。JSON_MERGE_PRESERVE()的已弃用同义词
JSON_MERGE_PATCH() (介绍5.7.22) 合并JSON文档,替换重复键的值
JSON_MERGE_PRESERVE() (介绍5.7.22) 合并JSON文档,保留重复的键
JSON_OBJECT() 创建JSON对象
JSON_PRETTY() (介绍5.7.22) 以易于阅读的格式打印JSON文档
JSON_QUOTE() 引用JSON文档
JSON_REMOVE() 从JSON文档中删除数据
JSON_REPLACE() 替换JSON文档中的值
JSON_SEARCH() JSON文档中值的路径
JSON_SET() 将数据插入JSON文档
JSON_STORAGE_SIZE() (介绍5.7.22) 用于存储JSON文档的二进制表示的空间
JSON_TYPE() JSON值类型
JSON_UNQUOTE() 取消引用JSON值
JSON_VALID() JSON值是否有效

MySQL 5.7.22和更高版本支持两个聚合JSON函数 JSON_ARRAYAGG()JSON_OBJECTAGG()。有关这些功能的说明,请参见 第12.20节“集合函数”

同样从MySQL 5.7.22开始:

  • 可以使用该JSON_PRETTY()函数以易于读取的格式对JSON值进行 “ 漂亮打印 ”。
  • 您可以使用看到给定JSON值占用了多少存储空间JSON_STORAGE_SIZE()

有关这两个函数的完整说明,请参见 第12.17.6节“ JSON实用程序函数”

12.17.2创建JSON值的函数

本节中列出的功能由组件元素组成JSON值。

  • JSON_ARRAY([val[, val\] ...])

    评估(可能为空)值列表,并返回包含这些值的JSON数组。

    mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
    +---------------------------------------------+
    | JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
    +---------------------------------------------+
    | [1, "abc", null, true, "11:30:24.000000"]   |
    +---------------------------------------------+
    
  • JSON_OBJECT([key, val[, key, val\] ...])

    评估键值对的列表(可能为空),并返回包含这些对的JSON对象。如果任何键名是NULL或参数个数是奇数,则会发生错误。

    mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
    +-----------------------------------------+
    | JSON_OBJECT('id', 87, 'name', 'carrot') |
    +-----------------------------------------+
    | {"id": 87, "name": "carrot"}            |
    +-----------------------------------------+
    
  • JSON_QUOTE(string)

    通过将字符串用双引号引起来并转义内部引号和其他字符来将utf8mb4字符串引为JSON值,然后将结果作为字符串返回 。NULL如果参数为,则 返回 NULL

    此函数通常用于生成有效的JSON字符串文字,以包含在JSON文档中。

    根据表12.22“ JSON_UNQUOTE()特殊字符转义序列”中所示的转义序列,使用反斜杠对某些特殊字符进行转义 。

    mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
    +--------------------+----------------------+
    | JSON_QUOTE('null') | JSON_QUOTE('"null"') |
    +--------------------+----------------------+
    | "null"             | "\"null\""           |
    +--------------------+----------------------+
    mysql> SELECT JSON_QUOTE('[1, 2, 3]');
    +-------------------------+
    | JSON_QUOTE('[1, 2, 3]') |
    +-------------------------+
    | "[1, 2, 3]"             |
    +-------------------------+
    

您还可以通过JSON使用将其他类型的值强制转换为该类型来 获取JSON值。有关更多信息,请参见 在JSON和非JSON值之间转换CAST(value AS JSON)

提供了两个生成JSON值的聚合函数(MySQL 5.7.22和更高版本)。 JSON_ARRAYAGG()返回结果集作为单个JSON数组,并 JSON_OBJECTAGG()返回结果集作为单个JSON对象。有关更多信息,请参见 第12.20节“聚合函数”

12.17.3搜索JSON值的函数

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

  • JSON_CONTAINS(target, candidate[, path\])

    通过返回1或0来指示给定的candidateJSON文档是否包含在targetJSON文档中,或者(如果提供了path 参数)指示是否 在目标内的特定路径上找到了候选对象。返回 NULL是否有任何参数是 NULL,或者path参数没有标识目标文档的一部分。如果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 是否有任何参数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 5.7.9和更高版本支持 -> 此函数的快捷方式,与2个参数一起使用,其中左侧是 JSON列标识符(不是表达式),右侧是要在列中匹配的JSON路径。

  • column->path

    在MySQL 5.7.9及更高版本中,与两个参数一起使用时,该 -> 运算符充当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)
    

    支持嵌套数组。使用的表达式的 ->求值就像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和更高版本中提供的一种改进的,取消引用的提取运算符。而 ->操作者简单地提取的值时, ->>在加法运算unquotes提取结果。换句话说,给定一个 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)
    

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

    此运算符也可以与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_doc, 则 返回; 文件内不存在;或 找不到。如果参数不是有效的JSON文档,任何 参数不是有效的路径表达式, 不是 或或 不是常数表达式,都会发生错误。 search_strpathNULLpathsearch_strjson_docpathone_or_all'one'``'all'escape_char

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

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

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

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

    有关匹配和转义字符行为的详细信息,请参阅的说明 LIKE12.7.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路径语法

12.17.4修改JSON值的函数

本节中的函数将修改JSON值并返回结果。

  • JSON_APPEND(json_doc, path, val[, path, val\] ...)

    将值附加到JSON文档中指定数组的末尾并返回结果。该函数JSON_ARRAY_APPEND() 在MySQL 5.7.9中已重命名;别名JSON_APPEND()现在在MySQL 5.7中已弃用,在MySQL 8.0中已删除。

  • JSON_ARRAY_APPEND(json_doc, path, val[, path, val\] ...)

    将值附加到JSON文档中指定数组的末尾并返回结果。返回 NULL是否有任何参数 NULL。如果json_doc参数不是有效的JSON文档或任何path参数不是有效的路径表达式或包含 `或通配符,则会发生错误 。

    路径值对从左到右评估。通过评估一对对生成的文档将成为新的价值,以此评估下一对。

    如果路径选择标量或对象值,则该值将自动包装在数组中,并将新值添加到该数组中。路径无法标识JSON文档中任何值的对将被忽略。

    mysql> SET @j = '["a", ["b", "c"], "d"]';
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
    +----------------------------------+
    | JSON_ARRAY_APPEND(@j, '$[1]', 1) |
    +----------------------------------+
    | ["a", ["b", "c", 1], "d"]        |
    +----------------------------------+
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
    +----------------------------------+
    | JSON_ARRAY_APPEND(@j, '$[0]', 2) |
    +----------------------------------+
    | [["a", 2], ["b", "c"], "d"]      |
    +----------------------------------+
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
    +-------------------------------------+
    | JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
    +-------------------------------------+
    | ["a", [["b", 3], "c"], "d"]         |
    +-------------------------------------+
    
    mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
    +------------------------------------+
    | JSON_ARRAY_APPEND(@j, '$.b', 'x')  |
    +------------------------------------+
    | {"a": 1, "b": [2, 3, "x"], "c": 4} |
    +------------------------------------+
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
    +--------------------------------------+
    | JSON_ARRAY_APPEND(@j, '$.c', 'y')    |
    +--------------------------------------+
    | {"a": 1, "b": [2, 3], "c": [4, "y"]} |
    +--------------------------------------+
    
    mysql> SET @j = '{"a": 1}';
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
    +---------------------------------+
    | JSON_ARRAY_APPEND(@j, '$', 'z') |
    +---------------------------------+
    | [{"a": 1}, "z"]                 |
    +---------------------------------+
    
  • JSON_ARRAY_INSERT(json_doc, path, val[, path, val\] ...)

    更新JSON文档,将其插入文档中的数组中并返回修改后的文档。返回 NULL是否有任何参数 NULL。如果json_doc参数不是有效的JSON文档,或者任何path参数不是有效的路径表达式,或者包含 `或通配符,或者不以数组元素标识符结尾,则会发生错误 。

    路径值对从左到右评估。通过评估一对对生成的文档将成为新的价值,以此评估下一对。

    路径无法标识JSON文档中任何数组的对将被忽略。如果路径标识数组元素,则将相应的值插入该元素位置,然后将所有后续值向右移动。如果路径标识了超出数组末尾的数组位置,则将值插入到数组末尾。

    mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
    +------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
    +------------------------------------+
    | ["a", "x", {"b": [1, 2]}, [3, 4]]  |
    +------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
    +--------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[100]', 'x') |
    +--------------------------------------+
    | ["a", {"b": [1, 2]}, [3, 4], "x"]    |
    +--------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
    +-----------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') |
    +-----------------------------------------+
    | ["a", {"b": ["x", 1, 2]}, [3, 4]]       |
    +-----------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');
    +---------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') |
    +---------------------------------------+
    | ["a", {"b": [1, 2]}, [3, "y", 4]]     |
    +---------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');
    +----------------------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') |
    +----------------------------------------------------+
    | ["x", "a", {"b": [1, 2]}, [3, 4]]                  |
    +----------------------------------------------------+
    

    较早的修改会影响数组中后续元素的位置,因此在同一JSON_ARRAY_INSERT()调用中的后续路径 应考虑到这一点。在最后一个示例中,第二条路径不插入任何内容,因为该路径在第一次插入后不再匹配任何内容。

  • JSON_INSERT(json_doc, path, val[, path, val\] ...)

    将数据插入JSON文档并返回结果。返回NULL是否有任何参数 NULL。如果json_doc参数不是有效的JSON文档或任何path参数不是有效的路径表达式或包含 `或通配符,则会发生错误 。

    The path-value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

    A path-value pair for an existing path in the document is ignored and does not overwrite the existing document value. A path-value pair for a nonexisting path in the document adds the value to the document if the path identifies one of these types of values:

    • A member not present in an existing object. The member is added to the object and associated with the new value.
    • A position past the end of an existing array. The array is extended with the new value. If the existing value is not an array, it is autowrapped as an array, then extended with the new value.

    Otherwise, a path-value pair for a nonexisting path in the document is ignored and has no effect.

    For a comparison of JSON_INSERT(), JSON_REPLACE(), and JSON_SET(), see the discussion of JSON_SET().

    mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
    mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
    +----------------------------------------------------+
    | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
    +----------------------------------------------------+
    | {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
    +----------------------------------------------------+
    

    结果中列出的第三个也是最后一个值是带引号的字符串,而不是像第二个那样的数组(在输出中不带引号);不会将值强制转换为JSON类型。要将数组插入为数组,必须显式执行此类转换,如下所示:

    mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON));
    +------------------------------------------------------------------+
    | JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON)) |
    +------------------------------------------------------------------+
    | {"a": 1, "b": [2, 3], "c": [true, false]}                        |
    +------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
  • JSON_MERGE(json_doc, json_doc[, json_doc\] ...)

    合并两个或多个JSON文档。同义词 JSON_MERGE_PRESERVE(); 在MySQL 5.7.22中已弃用,并可能在将来的版本中删除。

    mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
    +---------------------------------------+
    | JSON_MERGE('[1, 2]', '[true, false]') |
    +---------------------------------------+
    | [1, 2, true, false]                   |
    +---------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS\G
    *************************** 1. row ***************************
      Level: Warning
       Code: 1287
    Message: 'JSON_MERGE' is deprecated and will be removed in a future release. \
     Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead
    1 row in set (0.00 sec)
    

    有关其他示例,请参见的条目 JSON_MERGE_PRESERVE()

  • JSON_MERGE_PATCH(json_doc, json_doc[, json_doc\] ...)

    对两个或多个JSON文档 执行 符合RFC 7396的合并,并返回合并的结果,而不会保留具有重复密钥的成员。如果至少有一个作为参数传递给该函数的文档无效,则会引发错误。

    注意

    有关此函数和之间的区别的说明和示例JSON_MERGE_PRESERVE(),请参阅 JSON_MERGE_PATCH()与JSON_MERGE_PRESERVE()的比较

    JSON_MERGE_PATCH() 执行如下合并:

    1. 如果第一个参数不是对象,则合并的结果与将空对象与第二个参数合并的结果相同。
    2. 如果第二个参数不是对象,则合并的结果为第二个参数。
    3. 如果两个参数都是对象,则合并结果是具有以下成员的对象:
      • 第一个对象的所有成员在第二个对象中不具有具有相同键的对应成员。
      • 第二个对象的所有成员,在第一个对象中没有对应的键,并且其值不是JSON null文字。
      • 具有第一个和第二个对象中都存在且其第二个对象中的值不是JSON null 文字的键的所有成员。这些成员的值是将第一个对象中的值与第二个对象中的值递归合并的结果。

    有关更多信息,请参见 JSON值的规范化,合并和自动包装

    mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
    +---------------------------------------------+
    | JSON_MERGE_PATCH('[1, 2]', '[true, false]') |
    +---------------------------------------------+
    | [true, false]                               |
    +---------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
    +-------------------------------------------------+
    | JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}') |
    +-------------------------------------------------+
    | {"id": 47, "name": "x"}                         |
    +-------------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PATCH('1', 'true');
    +-------------------------------+
    | JSON_MERGE_PATCH('1', 'true') |
    +-------------------------------+
    | true                          |
    +-------------------------------+
    
    mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');
    +------------------------------------------+
    | JSON_MERGE_PATCH('[1, 2]', '{"id": 47}') |
    +------------------------------------------+
    | {"id": 47}                               |
    +------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }',
         >     '{ "a": 3, "c":4 }');
    +-----------------------------------------------------------+
    | JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |
    +-----------------------------------------------------------+
    | {"a": 3, "b": 2, "c": 4}                                  |
    +-----------------------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }',
         >     '{ "a": 5, "d":6 }');
    +-------------------------------------------------------------------------------+
    | JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }') |
    +-------------------------------------------------------------------------------+
    | {"a": 5, "b": 2, "c": 4, "d": 6}                                              |
    +-------------------------------------------------------------------------------+
    

    您可以使用此函数通过null在seond参数中指定相同成员的值来删除成员 ,如下所示:

    mysql> SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
    +--------------------------------------------------+
    | JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |
    +--------------------------------------------------+
    | {"a": 1}                                         |
    +--------------------------------------------------+
    

    本示例说明该函数以递归方式运行;也就是说,成员的值不限于标量,而是本身可以是JSON文档:

    mysql> SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');
    +----------------------------------------------------+
    | JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}') |
    +----------------------------------------------------+
    | {"a": {"x": 1, "y": 2}}                            |
    +----------------------------------------------------+
    

    JSON_MERGE_PATCH() 在MySQL 5.7.22和更高版本中受支持。

    JSON_MERGE_PATCH()与JSON_MERGE_PRESERVE()的比较。 的行为与的行为JSON_MERGE_PATCH()相同 JSON_MERGE_PRESERVE(),但以下两个例外:

    • JSON_MERGE_PATCH()删除与第二个对象中的键匹配的第一个对象中的任何成员,前提是与第二个对象中的键关联的值不是JSON null
    • 如果第二个对象的成员具有与第一个对象中的成员匹配的键,则 JSON_MERGE_PATCH() 第二个对象中的值替换第一个对象中的值,而 JSON_MERGE_PRESERVE() 第二个值附加到第一个值中。

    本示例比较了将两个相同的JSON对象(每个具有一个匹配的key "a")与以下两个函数的合并结果:

    mysql> SET @x = '{ "a": 1, "b": 2 }',
         >     @y = '{ "a": 3, "c": 4 }',
         >     @z = '{ "a": 5, "d": 6 }';
    
    mysql> SELECT  JSON_MERGE_PATCH(@x, @y, @z)    AS Patch,
        ->         JSON_MERGE_PRESERVE(@x, @y, @z) AS Preserve\G
    *************************** 1. row ***************************
       Patch: {"a": 5, "b": 2, "c": 4, "d": 6}
    Preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}
    
  • JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc\] ...)

    合并两个或多个JSON文档并返回合并的结果。返回NULL是否有任何参数 NULL。如果任何参数不是有效的JSON文档,则会发生错误。

    合并根据以下规则进行。有关更多信息,请参见 JSON值的规范化,合并和自动包装

    • 相邻数组合并为一个数组。
    • 相邻对象合并为单个对象。
    • 标量值将自动包装为数组,然后合并为数组。
    • 通过将对象自动包装为一个数组并将两个数组合并,可以合并相邻的数组和对象。
    mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');
    +------------------------------------------------+
    | JSON_MERGE_PRESERVE('[1, 2]', '[true, false]') |
    +------------------------------------------------+
    | [1, 2, true, false]                            |
    +------------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}');
    +----------------------------------------------------+
    | JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}') |
    +----------------------------------------------------+
    | {"id": 47, "name": "x"}                            |
    +----------------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PRESERVE('1', 'true');
    +----------------------------------+
    | JSON_MERGE_PRESERVE('1', 'true') |
    +----------------------------------+
    | [1, true]                        |
    +----------------------------------+
    
    mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}');
    +---------------------------------------------+
    | JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}') |
    +---------------------------------------------+
    | [1, 2, {"id": 47}]                          |
    +---------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }',
         >    '{ "a": 3, "c": 4 }');
    +--------------------------------------------------------------+
    | JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c":4 }') |
    +--------------------------------------------------------------+
    | {"a": [1, 3], "b": 2, "c": 4}                                |
    +--------------------------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }',
         >    '{ "a": 5, "d": 6 }');
    +----------------------------------------------------------------------------------+
    | JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }','{ "a": 5, "d": 6 }') |
    +----------------------------------------------------------------------------------+
    | {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}                                         |
    +----------------------------------------------------------------------------------+
    

    此功能已在MySQL 5.7.22中添加为的同义词 JSON_MERGE()JSON_MERGE()现在不推荐使用该功能,并且在将来的MySQL版本中可能会删除该 功能。

    This function is similar to but differs from JSON_MERGE_PATCH() in significant respects; see JSON_MERGE_PATCH() compared with JSON_MERGE_PRESERVE(), for more information.

  • JSON_REMOVE(json_doc, path[, path\] ...)

    Removes data from a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or is $ or contains a ` or wildcard.

    该path参数进行评估从左到右。通过评估一个路径生成的文档将成为评估下一个路径所依据的新值。

    如果要删除的元素在文档中不存在,这不是错误;在这种情况下,路径不会影响文档。

    mysql> SET @j = '["a", ["b", "c"], "d"]';
    mysql> SELECT JSON_REMOVE(@j, '$[1]');
    +-------------------------+
    | JSON_REMOVE(@j, '$[1]') |
    +-------------------------+
    | ["a", "d"]              |
    +-------------------------+
    
  • JSON_REPLACE(json_doc, path, val[, path, val\] ...)

    替换JSON文档中的现有值并返回结果。返回NULL是否有任何参数 NULL。如果json_doc参数不是有效的JSON文档或任何path参数不是有效的路径表达式或包含 `或通配符,则会发生错误 。

    路径值对从左到右评估。通过评估一对对生成的文档将成为新的价值,以此评估下一对。

    文档中现有路径的路径-值对使用新值覆盖现有文档值。文档中不存在的路径的路径-值对将被忽略,并且无效。

    为了进行比较 JSON_INSERT()JSON_REPLACE()以及 JSON_SET(),看到的讨论JSON_SET()

    mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
    mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
    +-----------------------------------------------------+
    | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
    +-----------------------------------------------------+
    | {"a": 10, "b": [2, 3]}                              |
    +-----------------------------------------------------+
    
  • JSON_SET(json_doc, path, val[, path, val\] ...)

    在JSON文档中插入或更新数据并返回结果。NULL如果任何参数是 NULL或path,则返回,如果给定,则不定位对象。如果json_doc参数不是有效的JSON文档或任何path参数不是有效的路径表达式或包含 `或通配符,则会发生错误 。

    路径值对从左到右评估。通过评估一对对生成的文档将成为新的价值,以此评估下一对。

    文档中现有路径的路径-值对使用新值覆盖现有文档值。如果路径中标识以下类型的值之一,则文档中不存在的路径的路径-值对会将值添加到文档中:

    • 现有对象中不存在的成员。成员被添加到对象并与新值关联。
    • 超出现有数组末尾的位置。用新值扩展数组。如果现有值不是数组,则将其自动包装为数组,然后使用新值进行扩展。

    否则,文档中不存在的路径的路径-值对将被忽略,并且无效。

    JSON_SET()JSON_INSERT()JSON_REPLACE()功能的关系:

    以下示例使用文档中存在的一个路径($.a)和不存在的另一路径()说明了这些差异$.c

    mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
    mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
    +-------------------------------------------------+
    | JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
    +-------------------------------------------------+
    | {"a": 10, "b": [2, 3], "c": "[true, false]"}    |
    +-------------------------------------------------+
    mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
    +----------------------------------------------------+
    | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
    +----------------------------------------------------+
    | {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
    +----------------------------------------------------+
    mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
    +-----------------------------------------------------+
    | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
    +-----------------------------------------------------+
    | {"a": 10, "b": [2, 3]}                              |
    +-----------------------------------------------------+
    
  • JSON_UNQUOTE(json_val)

    Unquotes JSON value and returns the result as a utf8mb4 string. Returns NULL if the argument is NULL. An error occurs if the value starts and ends with double quotes but is not a valid JSON string literal.

    在字符串中,某些序列具有特殊含义,除非NO_BACKSLASH_ESCAPES启用了SQL模式。每个序列都以反斜杠(\)开头,即 转义字符。MySQL可以识别 表12.22“ JSON_UNQUOTE()特殊字符转义序列”中所示的转义序列。对于所有其他转义序列,将忽略反斜杠。即,将转义字符解释为好像没有转义。例如,\xis x。这些序列区分大小写。例如, \b被解释为退格,但 \B被解释为B

    表12.22 JSON_UNQUOTE()特殊字符转义序列

    转义序列 序列表示的字符
    \" 双引号(")字符
    \b 退格字符
    \f 换页符
    \n 换行符(换行符)
    \r 回车符
    \t 制表符
    \\ 反斜杠(\)字符
    \uXXXX Unicode值的UTF-8字节 XXXX

    这里显示了使用此功能的两个简单示例:

    mysql> SET @j = '"abc"';
    mysql> SELECT @j, JSON_UNQUOTE(@j);
    +-------+------------------+
    | @j    | JSON_UNQUOTE(@j) |
    +-------+------------------+
    | "abc" | abc              |
    +-------+------------------+
    mysql> SET @j = '[1, 2, 3]';
    mysql> SELECT @j, JSON_UNQUOTE(@j);
    +-----------+------------------+
    | @j        | JSON_UNQUOTE(@j) |
    +-----------+------------------+
    | [1, 2, 3] | [1, 2, 3]        |
    +-----------+------------------+
    

    以下示例集显示了如何 JSON_UNQUOTENO_BACKSLASH_ESCAPES 禁用和启用的情况下进行转义 :

    mysql> SELECT @@sql_mode;
    +------------+
    | @@sql_mode |
    +------------+
    |            |
    +------------+
    
    mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');
    +------------------------------+
    | JSON_UNQUOTE('"\\t\\u0032"') |
    +------------------------------+
    |       2                           |
    +------------------------------+
    
    mysql> SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';
    mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');
    +------------------------------+
    | JSON_UNQUOTE('"\\t\\u0032"') |
    +------------------------------+
    | \t\u0032                     |
    +------------------------------+
    
    mysql> SELECT JSON_UNQUOTE('"\t\u0032"');
    +----------------------------+
    | JSON_UNQUOTE('"\t\u0032"') |
    +----------------------------+
    |       2                         |
    +----------------------------+
    

12.17.5返回JSON值属性的函数

本节中的函数返回JSON值的属性。

  • JSON_DEPTH(json_doc)

    返回JSON文档的最大深度。NULL如果参数为,则 返回 NULL。如果参数不是有效的JSON文档,则会发生错误。

    空数组,空对象或标量值的深度为1。仅包含深度1的元素的非空数组或仅包含深度1的成员值的非空对象的深度为2。否则,JSON文档的深度大于2。

    mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
    +------------------+------------------+--------------------+
    | JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
    +------------------+------------------+--------------------+
    |                1 |                1 |                  1 |
    +------------------+------------------+--------------------+
    mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
    +------------------------+------------------------+
    | JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') |
    +------------------------+------------------------+
    |                      2 |                      2 |
    +------------------------+------------------------+
    mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');
    +-------------------------------+
    | JSON_DEPTH('[10, {"a": 20}]') |
    +-------------------------------+
    |                             3 |
    +-------------------------------+
    
  • JSON_LENGTH(json_doc[, path\])

    返回JSON文档的长度,或者,如果提供了path参数,则返回 由路径标识的文档中值的长度。返回NULL是否有任何自变量 NULL或自path 变量不能标识文档中的值。如果json_doc参数不是有效的JSON文档,或者 path参数不是有效的路径表达式或包含`或 通配符,则会发生错误。

    文件长度确定如下:

    • 标量的长度为1。
    • 数组的长度是数组元素的数量。
    • 对象的长度是对象成员的数量。
    • 该长度不计算嵌套数组或对象的长度。
    mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
    +---------------------------------+
    | JSON_LENGTH('[1, 2, {"a": 3}]') |
    +---------------------------------+
    |                               3 |
    +---------------------------------+
    mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
    +-----------------------------------------+
    | JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
    +-----------------------------------------+
    |                                       2 |
    +-----------------------------------------+
    mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
    +------------------------------------------------+
    | JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
    +------------------------------------------------+
    |                                              1 |
    +------------------------------------------------+
    
  • JSON_TYPE(json_val)

    返回utf8mb4指示JSON值类型的字符串。可以是对象,数组或标量类型,如下所示:

    mysql> SET @j = '{"a": [10, true]}';
    mysql> SELECT JSON_TYPE(@j);
    +---------------+
    | JSON_TYPE(@j) |
    +---------------+
    | OBJECT        |
    +---------------+
    mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));
    +------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@j, '$.a')) |
    +------------------------------------+
    | ARRAY                              |
    +------------------------------------+
    mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));
    +---------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) |
    +---------------------------------------+
    | INTEGER                               |
    +---------------------------------------+
    mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]'));
    +---------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')) |
    +---------------------------------------+
    | BOOLEAN                               |
    +---------------------------------------+
    

    JSON_TYPE()返回 NULL参数是否为 NULL

    mysql> SELECT JSON_TYPE(NULL);
    +-----------------+
    | JSON_TYPE(NULL) |
    +-----------------+
    | NULL            |
    +-----------------+
    

    如果参数不是有效的JSON值,则会发生错误:

    mysql> SELECT JSON_TYPE(1);
    ERROR 3146 (22032): Invalid data type for JSON data in argument 1
    to function json_type; a JSON string or JSON type is required.
    

    对于NULL非错误结果,以下列表描述了可能的 JSON_TYPE()返回值:

    • 纯JSON类型:
      • OBJECT:JSON对象
      • ARRAY:JSON数组
      • BOOLEAN:JSON正确和错误文字
      • NULL:JSON空文字
    • 数值类型:
    • 时间类型:
    • 字符串类型:
    • 二进制类型:
    • 所有其他类型:
      • OPAQUE (原始位)
  • JSON_VALID(val)

    返回0或1以指示值是否有效JSON。NULL如果参数为,则 返回NULL

    mysql> SELECT JSON_VALID('{"a": 1}');
    +------------------------+
    | JSON_VALID('{"a": 1}') |
    +------------------------+
    |                      1 |
    +------------------------+
    mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
    +---------------------+-----------------------+
    | JSON_VALID('hello') | JSON_VALID('"hello"') |
    +---------------------+-----------------------+
    |                   0 |                     1 |
    +---------------------+-----------------------+
    

12.17.6 JSON实用程序功能

本节介绍了作用于JSON值的实用程序函数,或可以解析为JSON值的字符串。 JSON_PRETTY()以易于阅读的格式输出JSON值。 JSON_STORAGE_SIZE()显示给定JSON值使用的存储空间量。

  • JSON_PRETTY(json_val)

    提供漂亮的JSON值打印,类似于用PHP以及其他语言和数据库系统实现的JSON值。提供的值必须是JSON值或JSON值的有效字符串表示形式。此值中存在多余的空格和换行符对输出没有影响。对于NULL值,函数返回NULL。如果该值不是JSON文档,或者无法将其解析为一个文档,则该函数将失败并显示错误。

    此函数的输出格式遵循以下规则:

    • 每个数组元素或对象成员都显示在单独的行上,与其父级相比缩进了一个附加级别。
    • 每个缩进级别都会添加两个前导空格。
    • 在分隔两个元素或成员的换行符之前,将逗号分隔单个数组元素或对象成员打印出来。
    • 对象成员的键和值用冒号分隔,后跟一个空格(' : ')。
    • 空对象或数组将打印在一行上。左括号和右括号之间没有印刷空间。
    • 字符串标量和键名中的特殊字符使用该JSON_QUOTE()函数使用的相同规则进行转义 。
    mysql> SELECT JSON_PRETTY('123'); # scalar
    +--------------------+
    | JSON_PRETTY('123') |
    +--------------------+
    | 123                |
    +--------------------+
    
    mysql> SELECT JSON_PRETTY("[1,3,5]"); # array
    +------------------------+
    | JSON_PRETTY("[1,3,5]") |
    +------------------------+
    | [
      1,
      3,
      5
    ]      |
    +------------------------+
    
    mysql> SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}'); # object
    +---------------------------------------------+
    | JSON_PRETTY('{"a":"10","b":"15","x":"25"}') |
    +---------------------------------------------+
    | {
      "a": "10",
      "b": "15",
      "x": "25"
    }   |
    +---------------------------------------------+
    
    mysql> SELECT JSON_PRETTY('["a",1,{"key1":
         >    "value1"},"5",     "77" ,
         >       {"key2":["value3","valueX",
         > "valueY"]},"j", "2"   ]')\G  # nested arrays and objects
    *************************** 1. row ***************************
    JSON_PRETTY('["a",1,{"key1":
                 "value1"},"5",     "77" ,
                    {"key2":["value3","valuex",
              "valuey"]},"j", "2"   ]'): [
      "a",
      1,
      {
        "key1": "value1"
      },
      "5",
      "77",
      {
        "key2": [
          "value3",
          "valuex",
          "valuey"
        ]
      },
      "j",
      "2"
    ]
    

    在MySQL 5.7.22中添加。

  • JSON_STORAGE_SIZE(json_val)

    此函数返回用于存储JSON文档的二进制表示形式的字节数。当参数为JSON列时,这是用于存储JSON文档的空间。json_val 必须是有效的JSON文档或可以解析为一个字符串。如果是字符串,该函数将通过将字符串解析为JSON并将其转换为二进制来创建的JSON二进制表示形式返回存储空间量。NULL如果参数为,则返回NULL

    当json_val不是NULL,并且不是或无法成功解析为JSON文档时,将导致错误。

    为了说明此函数在以JSON列作为参数时的行为 ,我们创建了一个jtable包含 JSONjcol的表,在表中插入JSON值,然后使用来获得该列使用的存储空间 JSON_STORAGE_SIZE(),如下所示:

    mysql> CREATE TABLE jtable (jcol JSON);
    Query OK, 0 rows affected (0.42 sec)
    
    mysql> INSERT INTO jtable VALUES
        ->     ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
    Query OK, 1 row affected (0.04 sec)
    
    mysql> SELECT
        ->     jcol,
        ->     JSON_STORAGE_SIZE(jcol) AS Size
        -> FROM jtable;
    +-----------------------------------------------+------+
    | jcol                                          | Size |
    +-----------------------------------------------+------+
    | {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |
    +-----------------------------------------------+------+
    1 row in set (0.00 sec)
    

    根据的输出 JSON_STORAGE_SIZE(),插入到列中的JSON文档占用47个字节。更新后,该函数将显示用于新设置值的存储:

    mysql> UPDATE jtable
    mysql>     SET jcol = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
    Query OK, 1 row affected (0.04 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT
        ->     jcol,
        ->     JSON_STORAGE_SIZE(jcol) AS Size
        -> FROM jtable;
    +------------------------------------------------+------+
    | jcol                                           | Size |
    +------------------------------------------------+------+
    | {"a": 4.55, "b": "wxyz", "c": "[true, false]"} |   56 |
    +------------------------------------------------+------+
    1 row in set (0.00 sec)
    

    此函数还显示当前用于在用户变量中存储JSON文档的空间:

    mysql> SET @j = '[100, "sakila", [1, 3, 5], 425.05]';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
    +------------------------------------+------+
    | @j                                 | Size |
    +------------------------------------+------+
    | [100, "sakila", [1, 3, 5], 425.05] |   45 |
    +------------------------------------+------+
    1 row in set (0.00 sec)
    
    mysql> SET @j = JSON_SET(@j, '$[1]', "json");
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
    +----------------------------------+------+
    | @j                               | Size |
    +----------------------------------+------+
    | [100, "json", [1, 3, 5], 425.05] |   43 |
    +----------------------------------+------+
    1 row in set (0.00 sec)
    
    mysql> SET @j = JSON_SET(@j, '$[2][0]', JSON_ARRAY(10, 20, 30));
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
    +---------------------------------------------+------+
    | @j                                          | Size |
    +---------------------------------------------+------+
    | [100, "json", [[10, 20, 30], 3, 5], 425.05] |   56 |
    +---------------------------------------------+------+
    1 row in set (0.00 sec)
    

    对于JSON文字,此函数还返回当前使用的存储空间,如下所示:

    mysql> SELECT
        ->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
        ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
        ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
        ->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
    +----+----+----+----+
    | A  | B  | C  | D  |
    +----+----+----+----+
    | 45 | 44 | 47 | 56 |
    +----+----+----+----+
    1 row in set (0.00 sec)
    

    此功能已在MySQL 5.7.22中添加。

posted @ 2020-07-10 18:48  尘山海  阅读(325)  评论(0编辑  收藏  举报