如何在MySQL中向JSON类型列的对象数组追加新值或新数组

前置要求 #

你使用的MySQL版本需要 >= 5.7，该版本开始原生支持JSON数据类型及配套操作函数，可直接通过内置函数实现需求。

场景1：追加单个值4 #

使用JSON_ARRAY_APPEND函数即可实现，该函数接收三个参数：待操作的JSON字段、要操作的JSON路径、要追加的元素：

UPDATE `table`
-- 如果你存储JSON数据的列名不是id，请替换为实际列名
SET `id` = JSON_ARRAY_APPEND(`id`, '$.id', 4)
-- 可按需添加WHERE条件筛选要更新的行，例如WHERE 主键列 = xxx
;

执行后原{"id": [1,2,3]}会自动更新为{"id": [1,2,3,4]}。

场景2：追加整个数组[4,5,6] #

推荐使用JSON_MERGE_PRESERVE合并数组的方案，代码更简洁，也适合追加长数组的场景：

UPDATE `table`
SET `id` = JSON_SET(
    `id`,
    '$.id',
    JSON_MERGE_PRESERVE(
        JSON_EXTRACT(`id`, '$.id'), -- 提取原JSON中id对应的数组
        JSON_ARRAY(4,5,6) -- 要追加的新数组
    )
)
-- 按需添加WHERE条件
;

执行后原{"id": [1,2,3]}会更新为{"id": [1,2,3,4,5,6]}。

如果追加的数组元素较少，也可以嵌套调用JSON_ARRAY_APPEND实现：

UPDATE `table`
SET `id` = JSON_ARRAY_APPEND(
    JSON_ARRAY_APPEND(
        JSON_ARRAY_APPEND(`id`, '$.id', 4),
    '$.id', 5),
'$.id', 6);

结果验证 #

更新完成后可执行以下语句查看格式化后的JSON内容，确认更新是否符合预期：

SELECT JSON_PRETTY(`id`) FROM `table`
-- 按需添加WHERE条件
;

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