|
函数 |
说明 |
|
JSON_ARRAY_APPEND() |
将数据追加到 JSON 文档 |
|
JSON_ARRAY_INSERT() |
插入到 JSON 数组中 |
|
JSON_INSERT() |
将数据插入 JSON 文档 |
|
JSON_MERGE_PATCH() |
合并 JSON 文档，替换重复键的值 |
|
JSON_REMOVE() |
从 JSON 文档中删除数据 |
|
JSON_REPLACE() |
替换 JSON 文档中的值 |
|
JSON_SET() |
将数据插入 JSON 文档 |
|
JSON_UNQUOTE() |
取消引用 JSON 值 |
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
- 将值附加到 JSON 文档中指定数组的末尾并返回结果。如果任何参数是 则返回。如果 json_doc 参数不是有效的 JSON 文档或任何路径参数不是有效的路径表达式或包含或通配符，则会发生错误。NULLNULL***
- 路径值对从左到右进行评估。通过评估一对生成的文档成为评估下一对的新值
- 如果路径选择标量或对象值，则该值将自动包装在数组中，并将新值添加到该数组中。路径未标识 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 文档，插入文档中的数组并返回修改后的文档。如果任何参数是 则返回。如果 json_doc 参数不是有效的 JSON 文档或任何路径参数不是有效的路径表达式或包含或通配符或不以数组元素标识符结尾，则会发生错误。NULLNULL***
- 路径值对从左到右进行评估。通过评估一对生成的文档成为评估下一对的新值。
- 路径未标识 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 文档并返回结果。如果任何参数是 则返回。如果 json_doc 参数不是有效的 JSON 文档或任何路径参数不是有效的路径表达式或包含或通配符，则会发生错误。NULLNULL***
- 路径值对从左到右进行评估。通过评估一对生成的文档成为评估下一对的新值。
- 文档中现有路径的路径值对将被忽略，并且不会覆盖现有文档值。如果路径标识以下类型的值之一，则文档中不存在路径的路径-值对会将值添加到文档中：
- 现有对象中不存在的成员。该成员被添加到对象并与新值相关联。
- 超过现有数组末尾的位置。使用新值扩展数组。如果现有值不是数组，则将其自动包装为数组，然后使用新值进行扩展。
- 否则，文档中不存在路径的路径-值对将被忽略且无效。
示例
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_PATCH(json_doc, json_doc[, json_doc] ...)
- 执行两个或多个 JSON 文档的 RFC 7396 兼容合并并返回合并结果，而不保留具有重复键的成员。如果作为参数传递给此函数的至少一个文档无效，则会引发错误。
- JSON_MERGE_PATCH() 执行如下合并：
- 如果第一个参数不是对象，则合并的结果与将空对象与第二个参数合并的结果相同。
- 如果第二个参数不是对象，则合并的结果是第二个参数。
- 如果两个参数都是对象，则合并的结果是具有以下成员的对象：
- 第一个对象的所有成员，在第二个对象中没有具有相同键的对应成员。
- 第二个对象的所有成员，在第一个对象中没有对应的键，并且其值不是 JSON 字面量。空值
- 所有成员的键同时存在于第一个和第二个对象中，并且其在第二个对象中的值不是 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}} |
+------------------------------------------------------------+
- MySQL 8.0.3 及更高版本支持 JSON_MERGE_PATCH()
- JSON_MERGE_PRESERVE()，有以下两个例外：
- JSON_MERGE_PATCH() 删除第一个对象中具有第二个对象中匹配键的任何成员，前提是与第二个对象中的键关联的值不是 JSON 。空值
- 如果第二个对象的某个成员的键与第一个对象中的成员匹配，则将第一个对象中的值替换为第二个对象中的值，而将第二个值附加到第一个值。 JSON_MERGE_PATCH()JSON_MERGE_PRESERVE()
- 此示例比较了将相同的 3 个 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_REMOVE(json_doc, path[, path] ...)
- 从 JSON 文档中删除数据并返回结果。如果任何参数是 则返回。如果 json_doc 参数不是有效的 JSON 文档，或者任何路径参数不是有效的路径表达式，或者是或包含通配符，则会发生错误。 NULLNULL$***
- 路径参数从左到右进行评估。通过评估一个路径生成的文档成为评估下一个路径的新值。
- 如果要删除的元素在文档中不存在，则不是错误；在这种情况下，路径不会影响文档。
示例
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 文档中的现有值并返回结果。如果任何参数是 则返回。如果 json_doc 参数不是有效的 JSON 文档或任何路径参数不是有效的路径表达式或包含或通配符，则会发生错误。NULLNULL***
- 路径值对从左到右进行评估。通过评估一对生成的文档成为评估下一对的新值。
- 文档中现有路径的路径-值对用新值覆盖现有文档值。文档中不存在路径的路径-值对将被忽略且无效。
- 在 MySQL 8.0.4 中，优化器可以对列执行部分就地更新，而不是删除旧文档并将新文档全部写入列。可以为使用 JSON_REPLACE() 函数并满足 JSON 值的部分更新中概述的条件的更新语句执行此优化。 JSON
示例
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 参数不是有效的 JSON 文档或任何路径参数不是有效的路径表达式或包含或通配符，则会发生错误。NULLNULL***
- 路径值对从左到右进行评估。通过评估一对生成的文档成为评估下一对的新值。
- 文档中现有路径的路径-值对用新值覆盖现有文档值。如果路径标识以下类型的值之一，则文档中不存在路径的路径-值对会将值添加到文档中：
- 现有对象中不存在的成员。该成员被添加到对象并与新值相关联。
- 超过现有数组末尾的位置。使用新值扩展数组。如果现有值不是数组，则将其自动包装为数组，然后使用新值进行扩展。
- 否则，文档中不存在路径的路径-值对将被忽略且无效。
- 在 MySQL 8.0.4 中，优化器可以对列执行部分就地更新，而不是删除旧文档并将新文档全部写入列。可以为使用 JSON_SET() 函数并满足 JSON 值的部分更新中概述的条件的更新语句执行此优化。 JSON
- JSON_SET()、JSON_INSERT() 和 JSON_REPLACE() 函数是相关的：
- JSON_SET() 替换现有值并添加不存在的值。
- JSON_INSERT() 插入值而不替换现有值。
- JSON_REPLACE() 仅替换现有值。
- 以下示例说明了这些差异，使用文档中存在的一个路径 () 和不存在的另一个路径 ()：$.a$.c
示例
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_REMOVE(json_doc, path[, path] ...)
- 从 JSON 文档中删除数据并返回结果。如果任何参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档或任何路径参数不是有效的路径表达式或者是 $ 或包含 * 或 ** 通配符，则会发生错误。
- 路径参数从左到右进行评估。通过评估一个路径生成的文档成为评估下一个路径的新值。
- 如果要删除的元素在文档中不存在，则不是错误；在这种情况下，路径不会影响文档。
示例
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 文档或任何路径参数不是有效的路径表达式或包含 * 或 ** 通配符，则会发生错误。
- 路径值对从左到右进行评估。通过评估一对生成的文档成为评估下一对的新值。
- 文档中现有路径的路径-值对用新值覆盖现有文档值。文档中不存在路径的路径-值对将被忽略且无效。
- 在 MySQL 8.0.4 中，优化器可以对 JSON 列执行部分就地更新，而不是删除旧文档并将新文档完整写入列。可以为使用 JSON_REPLACE() 函数并满足 JSON 值的部分更新中概述的条件的更新语句执行此优化
示例
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。如果 json_doc 参数不是有效的 JSON 文档或任何路径参数不是有效的路径表达式或包含 * 或 ** 通配符，则会发生错误。
- 路径值对从左到右进行评估。通过评估一对生成的文档成为评估下一对的新值。
- 文档中现有路径的路径-值对用新值覆盖现有文档值。如果路径标识以下类型的值之一，则文档中不存在路径的路径-值对会将值添加到文档中：
- 现有对象中不存在的成员。该成员被添加到对象并与新值相关联。
- 超过现有数组末尾的位置。使用新值扩展数组。如果现有值不是数组，则将其自动包装为数组，然后使用新值进行扩展。
- 否则，文档中不存在路径的路径-值对将被忽略且无效。
- 在 MySQL 8.0.4 中，优化器可以对 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 值并将结果作为字符串返回。如果参数是 则返回。如果值以双引号开头和结尾但不是有效的 JSON 字符串文字，则会发生错误。 utf8mb4NULLNULL
- 在字符串中，某些序列具有特殊含义，除非启用了 NO_BACKSLASH_ESCAPES SQL 模式。这些序列中的每一个都以反斜杠 () 开头，称为转义字符。 MySQL 识别表 12.23，“JSON_UNQUOTE() 特殊字符转义序列”中显示的转义序列。对于所有其他转义序列，反斜杠将被忽略。也就是说，转义字符被解释为好像没有转义。例如，只是 .这些序列区分大小写。例如，被解释为退格，但被解释为 . \\xx\b\BB
- 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] |
+-----------+---------------------------+
- 以下示例集显示了如何在禁用和启用NO_BACKSLASH_ESCAPES的情况下处理转义：JSON_UNQUOTE
示例
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 |
+------------------------------------------+