12.18.4 Functions That Modify JSON Values
源文档路径:MySQL :: MySQL 5.7 Reference Manual :: 12.18.4 Functions That Modify JSON Values
这部分的函数用于修改JSON值和返回结果。
-
JSON_APPEND(
json_doc
,path
,val
[,path
,val
] …)在一个JSON文档中的展示的数组的末尾添加一个值,并且返回结果。这个函数在MySQL 5.7.9中更名为
JSON_ARRAY_APPEND()
;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
参数不是一个合法的路径表达式,或者包含*
和**
通配符,或者不是以一个数组元素标识符结尾,会发生错误。路径-值对是从左到右评估的。通过计算一对创建的文档会成为供下一对计算的新值。
在文档中已经存在的路径的路径-值对会被忽略,而且不会覆盖已经存在的文档值。如果路径标识这些类型之一,则一个对应不存在路径的路径-值对会向文档中添加值:
- 一个没有在已经存在的对象中展示的成员。这个成员会被添加到对象并于新值联系。
- 在已经存在的数组末尾之后的位置。数组会用新值扩展。如果已经存在的值不是一个数组,则会自动包装成一个数组,然后扩展这个新值。
否则,对于文档中不存在路径的路径-值对会被忽略,而且不产生影响。
对于
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()
的同义词;在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
] …)执行一个RFC 7396服从性合并两个或者更多的JSON文档,并且返回合并的结果,不保留有重复键的程序。如果作为参数传递到这个函数的文档中至少有一个是非法的话,会发生错误。
注意:
关于这个函数和
JSON_MERGE_PRESERVE()
的差别的解释和示例,请浏览JSON_MERGE_PATCH() compared with JSON_MERGE_PRESERVE()。JSON_MERGE_PATCH()
执行如下的合并。- 如果第一个参数不是一个对象,合并结果与一个空对象和第二个参数合并的结果相同。
- 如果第二个参数不是一个对象,则合并的结果是第二个参数。
- 如果所有参数都是对象,合并结果是一个对象,含有以下成员:
- 第一个对象中的所有成员,在第二个对象中没有对应的相同键的成员。
- 第二个对象中的所有成员,在第一个对象中没有对应的键,且值不为JSON
null
字面量。 - 所有在第一个对象和第二个对象中的成员,且第二个对象中的值不是JSON
null
字面量。这些成员的值是递归合并第一个对象中的值和第二个对象中的值的结果。
要获取额外的信息,请查看Normalization, Merging, and Autowrapping of JSON Values。
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()
在MySQL 5.7.22及以后支持。JSON_MERGE_PATCH() compared with JSON_MERGE_PRESERVE().
JSON_MERGE_PATCH()
的行为与JSON_MERGE_PRESERVE()
相同,除了如下两个例外:JSON_MERGE_PATCH()
移除在第二个对象中有匹配键的第一个对象中的成员,提供第二个对象中不是JSONnull
的值的联系。- 如果第二个对象中有成员有匹配第一个对象中的成员的键,
JSON_MERGE_PATCH()
会用第二个对象中的值替换第一个对象中的值,而JSON_MERGE_PRESERVE()
会把第二个值添加给第一个值。
这个例子比较合并3个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文档,并返回合并后的结果。如果任何参数为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 版本中被移除。
此函数与 JSON_MERGE_PATCH()
有显著的不同之处;有关更多信息,请参见 JSON_MERGE_PATCH() compared with 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_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_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 值并将结果作为
utf8mb4
字符串返回。如果参数为NULL
,则返回NULL
。如果值以双引号开头和结尾,但不是一个有效的 JSON 字符串文字,则会发生错误。在字符串内,除非启用了
NO_BACKSLASH_ESCAPES
SQL 模式,否则某些序列具有特殊含义。这些序列以反斜杠(\
)开头,称为转义字符。MySQL 识别 表 12.23 “JSON_UNQUOTE() 特殊字符转义序列” 中所示的转义序列。对于所有其他转义序列,反斜杠被忽略。也就是说,转义的字符被解释为未转义的字符。例如,\x
就是x
。这些序列是区分大小写的。例如,\b
被解释为回退,但\B
被解释为B
。表 12.23 JSON_UNQUOTE() 特殊字符转义序列
转义序列 表示的字符 \"
双引号( "
)\b
退格符 \f
换页符 \n
换行符 \r
回车符 \t
制表符 \\
反斜杠( \
)\u*
XXXX*
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 | +----------------------------+