如何在MySQL的JSON类型列中追加数据？

要实现JSON数组列的元素追加而非覆盖，核心是利用数据库提供的JSON数组操作函数，同时处理列值为NULL的场景。以下是主流数据库的具体实现方案，以及对应的Java操作示例：

MySQL 实现 #

使用JSON_ARRAY_APPEND函数向已有JSON数组追加元素，配合IF函数处理列值为NULL的场景：

UPDATE myTable
SET columnJson = IF(columnJson IS NULL, 
                    '[{"id":"newId","name":"newName"}]',
                    JSON_ARRAY_APPEND(columnJson, '$', '{"id":"newId","name":"newName"}'))
WHERE id = "rowID1";

Java 代码示例 #

利用Jackson生成合法JSON字符串，通过预编译SQL避免注入：

import com.fasterxml.jackson.databind.ObjectMapper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

// 构造要追加的JSON元素
ObjectMapper mapper = new ObjectMapper();
Map<String, String> newItem = new HashMap<>();
newItem.put("id", "newId3");
newItem.put("name", "newName3");
String newItemJson = mapper.writeValueAsString(newItem);

// 执行更新操作
String sql = "UPDATE myTable SET columnJson = IF(columnJson IS NULL, ?, JSON_ARRAY_APPEND(columnJson, '$', ?)) WHERE id = ?";
try (Connection conn = getConnection()) { // 需自行实现getConnection获取数据库连接
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, "[" + newItemJson + "]"); // 列空时插入单元素数组
    pstmt.setString(2, newItemJson); // 追加的元素
    pstmt.setString(3, "rowID1");
    pstmt.executeUpdate();
} catch (SQLException | com.fasterxml.jackson.core.JsonProcessingException e) {
    e.printStackTrace();
}

PostgreSQL 实现 #

推荐使用jsonb类型（比json性能更好），用||操作符追加元素，COALESCE处理NULL：

UPDATE myTable
SET columnJson = COALESCE(columnJson, '[]'::jsonb) || '{"id":"newId","name":"newName"}'::jsonb
WHERE id = "rowID1";

如果是json类型，替换为json即可：

UPDATE myTable
SET columnJson = COALESCE(columnJson, '[]'::json) || '{"id":"newId","name":"newName"}'::json
WHERE id = "rowID1";

SQL Server 实现 #

使用JSON_MODIFY的append模式追加元素，CASE处理NULL：

UPDATE myTable
SET columnJson = CASE
                    WHEN columnJson IS NULL THEN '[{"id":"newId","name":"newName"}]'
                    ELSE JSON_MODIFY(columnJson, 'append $', JSON_QUERY('{"id":"newId","name":"newName"}'))
                 END
WHERE id = "rowID1";

通用注意事项 #

• 禁止手动拼接JSON字符串，使用Jackson/Gson等库生成合法JSON，避免格式错误。
• 必须使用PreparedStatement预编译SQL，防止SQL注入风险。
• 批量追加多个元素时，可先构造包含所有新元素的JSON数组，再一次性追加（不同数据库语法略有差异，比如MySQL的JSON_ARRAY_APPEND支持一次追加多个值）。

内容的提问来源于stack exchange，提问作者David