MySQL JSON_SET() 函数使用指南

MySQL JSON_SET() 函数在一个 JSON 文档中插入或更新数据并返回新的 JSON 文档。它相当于是 JSON_INSERT()JSON_REPLACE() 的组合。

JSON_SET() 语法

这里是 MySQL JSON_SET() 的语法:

JSON_SET(json, path, value[, path2, value2] ...)

参数

json
必需的。被修改的 JSON 文档。
path
必需的。一个有效的路径表达式,它不能包含 ***
value
必需的。要设置的数据。

返回值

JSON_SET() 函数在一个 JSON 文档中插入或更新数据并返回新的 JSON 文档。您可以提供多对 path-value 参数,以便一次设定多个数据。

如果 JSON 文档中存在参数中指定的路径,则更新路径匹配的值;如果不存在,则在对应的路径上插入数据。

如果 value 为字符串, JSON_SET() 函数会将其作为字符串写入到 JSON 文档中。

如果 JSON 文档或者路径为 NULL,此函数将返回 NULL

JSON_SET() 函数将在以下情况下返回错误:

  • 如果参数 json 不是有效的 JSON 文档,MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
  • 如果参数 path 不是有效的路径表达式或者其中包含 ***, MySQL 将会给出错误。

JSON_SET() 示例

让我们首先创建一个包含 JSON 对象的 JSON 文档:

SET @obj = '{"x": 1}';

现在让我们设置一些数据:

SELECT JSON_SET(@obj, '$.x', '10', '$.y', '[1, 2]');
+----------------------------------------------+
| JSON_SET(@obj, '$.x', '10', '$.y', '[1, 2]') |
+----------------------------------------------+
| {"x": "10", "y": "[1, 2]"}                   |
+----------------------------------------------+

我们发现,虽然已经设置成功,但还有些小问题,就是数组 [1, 2] 变成了 "[1, 2]"

这是因为,如果 value 参数为字符串, JSON_SET() 函数会将其作为字符串写入到 JSON 文档中。我们再看一个相似的例子:

SELECT JSON_SET(@obj, '$.x', '10', '$.y', '{"z": 2}');
+------------------------------------------------+
| JSON_SET(@obj, '$.x', '10', '$.y', '{"z": 2}') |
+------------------------------------------------+
| {"x": "10", "y": "{\"z\": 2}"}                 |
+------------------------------------------------+

为了解决这个问题,我们可以使用 CAST() 函数将数据转为 JSON 类型,比如:

SELECT JSON_SET(@obj, '$.x', '10', '$.y', CAST('[1, 2]' AS JSON));
+------------------------------------------------------------+
| JSON_SET(@obj, '$.x', '10', '$.y', CAST('[1, 2]' AS JSON)) |
+------------------------------------------------------------+
| {"x": "10", "y": [1, 2]}                                   |
+------------------------------------------------------------+