JSON数组操作函数

JSON_ARRAY_INSERT

在 JSON 数组的指定位置插入值，路径必须指向数组元素位置（如 $[0]、$.a[0]）。

语法：

JSON_ARRAY_INSERT(json_doc, path, value[, path2, value2] ...)

参数：

• json_doc：被修改的 JSON 文档（字符串类型）。

• path：插入位置的路径表达式，必须指向数组元素位置（如 $[0]、$.a[0]）。

• value：要插入的值。

• 支持多组 path-value 对，实现批量插入。

返回值：返回修改后的 JSON 文档字符串。

JSON_ARRAY_APPEND

向 JSON 文档中指定数组的末尾追加一个或多个值。

语法：

JSON_ARRAY_APPEND(json_doc, path, value[, path2, value2] ...)

参数：

• json_doc：被修改的 JSON 文档（字符串类型）。

• path：目标数组的路径表达式（如 $、$[1]、$.hobby）。

• value：要追加的值。

• 支持多组 path-value 对，实现批量追加。

返回值：返回修改后的 JSON 文档字符串。

基本操作

-- 建表
CREATE TABLE t (id INT, c2 JSON, tags JSON, PRIMARY KEY(id)) 'CONSISTENCY' = 'strong';

-- 初始数据
INSERT INTO t(id, tags, c2) VALUES
  (1, '["a","b","c"]',               '[1,[2,3]]'),
  (2, '[1,2,3]',                      '[1,2,3]'),
  (3, NULL,                           '"hello"'),
  (4, NULL,                           '{"key":"value"}'),
  (5, NULL,                           '{"name":"Tim","hobby":["car"]}'),
  (6, NULL,                           '{"field":"value"}'),
  (7, NULL,                           '{"tags":["java"],"categories":["tech"]}');

-- 在指定位置插入（原元素后移）
UPDATE t SET tags = JSON_ARRAY_INSERT(tags, '$[0]', 'first') WHERE id = 1;
-- 原值: ["a","b","c"]，结果: ["first","a","b","c"]

-- 在末尾追加
UPDATE t SET tags = JSON_ARRAY_APPEND(tags, '$', 'last') WHERE id = 2;
-- 原值: [1,2,3]，结果: [1,2,3,"last"]

-- 向嵌套数组操作（路径支持任意深度）
UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[1][0]', 'x') WHERE id = 1;
-- 原值: [1,[2,3]]，结果: [1,["x",2,3]]

-- 向对象内数组追加
UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, '$.hobby', 'food') WHERE id = 5;
-- 原值: {"name":"Tim","hobby":["car"]}，结果: {"name":"Tim","hobby":["car","food"]}

-- INSERT：路径指向非数组节点时报错
UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[0]', 'x') WHERE id = 3;
-- 原值: "hello"（标量），报错: Path does not point to array

UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[0]', 'x') WHERE id = 4;
-- 原值: {"key":"value"}（对象），报错: Path does not point to array

-- APPEND：非数组节点自动转为数组第一个元素，新值追加到末尾
UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, '$', 'world') WHERE id = 3;
-- 原值: "hello"，结果: ["hello","world"]

UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, '$', 'item') WHERE id = 4;
-- 原值: {"key":"value"}，结果: [{"key":"value"},"item"]

-- 对象内标量字段就地转为数组
UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, '$.field', 'new') WHERE id = 6;
-- 原值: {"field":"value"}，结果: {"field":["value","new"]}

批量操作（按参数顺序依次执行）

每对 path-value 顺序执行，前一步结果作为下一步输入，索引基于当前状态计算：

-- 批量插入：每步插入后索引随之变化
UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[0]', 'first', '$[3]', 'middle', '$[6]', 'last') WHERE id = 2;
-- 原值: [1,2,3]，结果: ["first",1,2,"middle",3,"last"]

-- 同一位置多次插入：后插入的排在前面
UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[0]', 'a', '$[0]', 'b') WHERE id = 2;
-- 原值: [1,2,3]，结果: ["b","a",1,2,3]

-- 批量追加到多个子数组
UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, '$.tags', 'sql', '$.tags', 'python', '$.categories', 'db') WHERE id = 7;
-- 原值: {"tags":["java"],"categories":["tech"]}
-- 结果: {"tags":["java","sql","python"],"categories":["tech","db"]}

索引越界

索引超出数组长度时自动插入到末尾（与 MySQL 一致）：

UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[100]', 'x') WHERE id = 2;
-- 原值: [1,2,3]，结果: [1,2,3,"x"]

PreparedStatement 参数化

path 和 value 均支持 ? 占位符。

// 初始数据 id=1: c2 = ["a","inserted","b","c","appended"]

// JSON_ARRAY_INSERT：在 $[2] 位置插入
PreparedStatement pstmt = conn.prepareStatement(
    "UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, ?, ?) WHERE id = ?");
pstmt.setString(1, "$[2]");
pstmt.setString(2, "prep_insert");
pstmt.setInt(3, 1);
pstmt.executeUpdate();
// 结果: ["a","inserted","prep_insert","b","c","appended"]

// JSON_ARRAY_APPEND：追加到末尾
pstmt = conn.prepareStatement(
    "UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, ?, ?) WHERE id = ?");
pstmt.setString(1, "$");
pstmt.setString(2, "prep_append");
pstmt.setInt(3, 1);
pstmt.executeUpdate();
// 结果: ["a","inserted","prep_insert","b","c","appended","prep_append"]

节点类型与 NULL 处理