MySQL JSON_INSERT() 函数使用指南

MySQL JSON_INSERT() 函数向一个 JSON 文档中插入数据并返回新的 JSON 文档。

JSON_INSERT() 语法

这里是 MySQL JSON_INSERT() 的语法:

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

参数

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

返回值

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

JSON_INSERT() 函数只能将数据插入到不存在路径。如果 JSON 文档中已经存在指定的路径,则不会插入数据。

如果 value 为字符串, JSON_INSERT() 函数会将其作为字符串写入到 JSON 文档中。为了保证写入到 JSON 文档中的值的类型正确,请对 value 使用 JSON 类型的数据。

如果 path$JSON_INSERT() 函数会返回原 JSON 文档。

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

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

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

JSON_INSERT() 示例

插入到数组

让我们首先创建一个 JSON 文档以便于演示下面的示例:

SET @array = '[1, [2, 3], {"a": [4, 5]}]';

让我们在数组的开头插入一个元素:

SELECT JSON_INSERT(@array, '$[0]', 0, '$[3]', 6);

这里,我们想要在数组的开头插入 0,在数组的尾部插入 6。让我们看一下结果:

+-------------------------------------------+
| JSON_INSERT(@array, '$[0]', 0, '$[3]', 6) |
+-------------------------------------------+
| [1, [2, 3], {"a": [4, 5]}, 6]             |
+-------------------------------------------+

我们发现,并没有在数据的开头($[0])插入数据。这是因为数组在 $[0] 位置已经有了值,因此不会插入。而数组在 $[3] 位置是没有数据的,因此 6 被插入到数组的尾部。

您还可以使用 JSON_ARRAY_APPEND() 向数组中插入数据。

插入 JSON 类型数据

除了插入简单的字面值,我们还可以插入复杂的 JSON 元素,比如数组和对象。

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

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

现在让我们插入一个值为 true 的成员 y:

SELECT JSON_INSERT(@obj, '$.y', 'true');
+----------------------------------+
| JSON_INSERT(@obj, '$.y', 'true') |
+----------------------------------+
| {"x": 1, "y": "true"}            |
+----------------------------------+

我们发现, true 变成了 "true"。而不是我们希望的 {"x": 1, "y": true}

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

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

或者

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

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

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