Documentation Home
MySQL 8.4 Reference Manual
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 8.4 Reference Manual  /  ...  /  Functions That Modify JSON Values

14.17.4 JSON 值修改函数

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

  • 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 参数不是有效的路径表达式或包含 * 或 ** 通配符或不以数组元素标识符结尾，則发生错误。

    路径-值对从左到右被评估。评估一个对的文档将成为下一个对的新值。

    对于没有识别任何数组的路径对，忽略该对。如果路径识别了数组元素，该对应的值将在该元素位置插入，推移任何后续值。如果路径识别了数组末尾后的位置，该对应的值将插入数组末尾。

    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() 调用中的后续路径应该考虑这个问题。在最后一个示例中，第二个路径插入 nothing，因为路径不再匹配任何内容 после第一个插入。

  • JSON_INSERT(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_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_ MERGE 的弃用同义词。

  • JSON_ MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

    执行RFC 7396 compliant 的 JSON 文档合并，并返回合并结果，不保留具有重复键的成员。函数如果至少有一个参数不是有效的JSON文档，则抛出错误。

    Note

    了解本函数和JSON_ MERGE_PRESERVE()之间的差异，请参阅JSON_ MERGE_PATCH() compared with JSON_ MERGE_PRESERVE()。

    JSON_ MERGE_PATCH()执行合并操作如下：

    1. 如果第一个参数不是对象，则合并结果与将空对象合并到第二个参数的结果相同。

    2. 如果第二个参数不是对象，那么 merge 的结果就是第二个参数。

    3. 如果两个参数都是对象，那么 merge 的结果是一个对象，其中包含以下成员：

      • 第一个对象中的所有成员，除非在第二个对象中存在相同的键。

      • 第二个对象中的所有成员，除非在第一个对象中不存在相同的键，并且该值不是 JSON null 字面量。

      • 同时存在于两个对象中的成员，如果在第二个对象中的值不是 JSON null 字面量，那么这些成员的值是通过递归地合并第一个对象中的值和第二个对象中的值来获得的。

    欲了解更多信息，请参阅JSON 值的 normalization、合并和自动包装。

    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，如以下示例所示：

    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() 与 JSON_ MERGE_PRESERVE() 的比较。 JSON_ MERGE_PATCH() 的行为与JSON_ MERGE_PRESERVE()相同，以下两个例外除外：

    • JSON_MERGE_PATCH() 将在第二个对象中找到匹配的键时，删除第一个对象中的相应成员，但前提是第二个对象中该键对应的值不是 JSON null。

    • 如果第二个对象有与第一个对象中成员相同的键，JSON_MERGE_PATCH() 将替换第一个对象中的值，而JSON_ MERGE_PRESERVE() 将将第二个值追加到第一个值末尾。

    以下示例比较了使用这两个函数对同三个 JSON 对象进行合并的结果，每个对象都有一个匹配键"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 值的 normalization、合并和自动生成。

    • 相邻数组被合并为单个数组。

    • 相邻对象被合并为单个对象。

    • 标量值将被自动包装为数组，并作为数组进行合并。

    • 相邻数组和对象被合并，通过自动包装对象为数组，然后将两个数组合并。

    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}                                         |
+----------------------------------------------------------------------------------+

    这函数与JSON_MERGE_PATCH()有significant的区别；请参阅JSON_ MERGE_PATCH()与JSON_ MERGE_PRESERVE()，了解更多信息。

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

    从JSON文档中删除数据并返回结果。如果任何参数为NULL，则返回NULL。如果json_doc参数不是有效的JSON文档或任何path参数不是有效的路径表达式或是$或包含*或**通配符，则发生错误。

    对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列进行部分、in-place 更新。这一优化可以在使用JSON_ REPLACE()函数的更新语句中进行，并且满足在Partial Updates of JSON Values中所述的条件。

    关于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_文档, 路径, 值[, 路径, 值] ...)

    将 JSON 文档中的数据插入或更新，并返回结果。如果json_文档或路径为NULL，则返回NULL。否则，如果路径在给定时不locate 对象，则发生错误。此外，如果json_文档参数不是有效的 JSON 文档，或者任何路径参数不是有效的路径表达式，或者包含*或**通配符，则发生错误。

    路径-值对从左到右进行评估。评估一个对后，文档将变为新的值，以便评估下一个对。

    对于现有路径在文档中的路径-值对，将现有文档值覆盖为新值。对于不存在的路径在文档中的路径-值对，如果路径标识以下类型的值，则将值添加到文档中：

    • 对象中的成员，但该成员不在存在的对象中。将成员添加到对象中，并将其关联到新值。

    • 数组中的位置，位于现有数组末尾。将数组扩展以包含新值。如果现有值不是数组，则将其自动包装为数组，然后扩展以包含新值。

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

    优化器可以对JSON列进行部分、在-place更新，而不是删除旧文档并将新文档写入整个列中。这一优化可以在使用JSON_SET()函数的更新语句中进行，且满足Partial Updates of JSON Values中的条件。

    相关的函数是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)

    去除 JSON 值的引号并将结果转换为 utf8mb4 字符串。如果参数是 NULL，则返回 NULL。如果值以双引号开头和结尾，但不是有效的 JSON 字符串字面量，则发生错误。

    在字符串中，某些序列具有特殊含义，除非启用了NO_BACKSLASH_ESCAPES SQL 模式。每个这些序列都以反斜杠 (\) 开头，称为 转义字符。MySQL 认识在表 14.23，“JSON_UNQUOTE() Special Character Escape Sequences” 中显示的转义序列。对于所有其他转义序列，反斜杠被忽略，即被转义的字符将被解释为未转义的字符。例如，\x 只是 x。这些序列是区分大小写的。例如，\b 被解释为退格键，但 \B 被解释为 B。

    表 14.23 JSON_UNQUOTE() Special Character Escape Sequences

    Escape Sequence Character Represented by Sequence
    \" 双引号 (") 字符
    \b 退格键字符
    \f 换页符字符
    \n 换行符（回车）字符
    \r 回车符字符
    \t 制表符字符
    \\ 反斜杠（\)字符
    \uXXXX UTF-8字节，Unicode值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_UNQUOTE的示例，展示了在NO_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                         |
+----------------------------+

PREV   HOME   UP   NEXT
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb