MySQL :: MySQL 9.0 参考手册 :: 14.17.4 修改 JSON 值的函数

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / 修改 JSON 值的函数

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

路径-值对从左到右求值。求值一对后生成的新文档将成为求值下一对时的新值。

如果路径在 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 参数不是有效的路径表达式或包含 * 或 ** 通配符，则会发生错误。
路径-值对从左到右求值。求值一对后生成的新文档将成为求值下一对时的新值。
如果文档中存在路径，则将忽略该路径-值对，并且不会覆盖现有的文档值。如果文档中不存在路径，则如果路径标识以下类型的值之一，则路径-值对会将值添加到文档中
- 现有对象中不存在的成员。该成员将添加到对象中，并与新值关联。
- 现有数组末尾之后的位置。数组将使用新值进行扩展。如果现有值不是数组，则将其自动包装为数组，然后使用新值进行扩展。
否则，如果文档中不存在路径，则将忽略该路径-值对，并且不会产生任何影响。
有关 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_PRESERVE() 的已弃用同义词。

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

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

注意

有关此函数与 JSON_MERGE_PRESERVE() 之间差异的说明和示例，请参阅 JSON_MERGE_PATCH() 与 JSON_MERGE_PRESERVE() 相比。

JSON_MERGE_PATCH() 按如下方式执行合并

如果第一个参数不是对象，则合并的结果与将空对象与第二个参数合并的结果相同。
如果第二个参数不是对象，则合并的结果是第二个参数。
如果两个参数都是对象，则合并的结果是一个具有以下成员的对象
- 第一个对象中所有在第二个对象中没有相同键的对应成员的成员。
- 第二个对象中所有在第一个对象中没有对应键的成员，并且其值不是 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 作为同一成员的值，如下所示

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 null，则 JSON_MERGE_PATCH() 会删除第一个对象中的成员。
如果第二个对象中存在与第一个对象中的成员匹配的键，则 JSON_MERGE_PATCH() 会将第一个对象中的值替换为第二个对象中的值，而 JSON_MERGE_PRESERVE() 会将第二个值追加到第一个值。

此示例比较了使用这两个函数分别合并具有相同匹配键 "a" 的 3 个相同 JSON 对象的结果

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

此函数类似于JSON_MERGE_PATCH()，但在重要方面有所不同；有关更多信息，请参阅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 列执行部分就地更新，而不是删除旧文档并将新文档整体写入该列。可以使用 JSON_REPLACE() 函数且满足JSON 值的部分更新中概述的条件的更新语句可以执行此优化。
有关 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 文档中插入或更新数据并返回结果。如果 json_doc 或 path 为 NULL，或者如果 path（如果给出）未找到对象，则返回 NULL。否则，如果 json_doc 参数不是有效的 JSON 文档，或者任何 path 参数不是有效的路径表达式或包含 * 或 ** 通配符，则会发生错误。
路径-值对从左到右求值。求值一对后生成的新文档将成为求值下一对时的新值。
文档中现有路径的路径值对会使用新值覆盖现有文档值。如果路径标识以下类型的值之一，则文档中不存在的路径的路径值对会将该值添加到文档中
- 现有对象中不存在的成员。该成员将添加到对象中，并与新值关联。
- 现有数组末尾之后的位置。数组将使用新值进行扩展。如果现有值不是数组，则将其自动包装为数组，然后使用新值进行扩展。
否则，如果文档中不存在路径，则将忽略该路径-值对，并且不会产生任何影响。
优化器可以对 JSON 列执行部分就地更新，而不是删除旧文档并将新文档整体写入该列。可以使用 JSON_SET() 函数且满足JSON 值的部分更新中概述的条件的更新语句可以执行此优化。
JSON_SET()、JSON_INSERT() 和 JSON_REPLACE() 函数相关
- 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() 特殊字符转义序列”中显示的转义序列。对于所有其他转义序列，反斜杠将被忽略。也就是说，转义字符将被解释为好像没有转义一样。例如，\x 只是 x。这些序列区分大小写。例如，\b 被解释为退格，但 \B 被解释为 B。

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

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

以下显示了此函数的两个简单用例

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