如何使用JSON_MERGE_PATCH对JSON数组内指定对象执行合并更新操作

核心思路 #

• 先通过JSON_SEARCH定位到class=Identity的元素在数组中的索引
• 拼接得到目标元素的完整JSON路径
• 用JSON_EXTRACT取出原对象，传入JSON_MERGE_PATCH完成属性合并
• 最后用JSON_SET把合并后的对象写回原数组的对应位置

单匹配项更新方案 #

适合数组内只有1个class=Identity对象的场景，假设存储JSON数组的表名为game_data，JSON字段名为attrs，示例更新语句如下：

UPDATE game_data
SET attrs = JSON_SET(
    attrs,
    -- 拼接目标对象的JSON路径
    CONCAT('$[', SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(attrs, 'one', 'Identity', NULL, '$[*].class')), '[', -1), ']', 1), ']'),
    -- 对原对象执行合并补丁
    JSON_MERGE_PATCH(
        JSON_EXTRACT(
            attrs,
            CONCAT('$[', SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(attrs, 'one', 'Identity', NULL, '$[*].class')), '[', -1), ']', 1), ']')
        ),
        '{"x": 5}'
    )
)
-- 过滤无匹配元素的行，避免执行报错
WHERE JSON_CONTAINS_PATH(attrs, 'one', '$[*].class') 
AND JSON_SEARCH(attrs, 'one', 'Identity', NULL, '$[*].class') IS NOT NULL;

多匹配项通用方案（MySQL 8.0+ 支持） #

如果数组内存在多个符合条件的对象，或者需要更复杂的过滤逻辑，可以用JSON_TABLE把数组拆成临时行处理，再聚合回数组，示例查询语句如下（如需更新直接把结果写回原表即可）：

SELECT 
    JSON_ARRAYAGG(
        CASE 
            WHEN j.class = 'Identity' THEN JSON_MERGE_PATCH(j.obj, '{"x":5}')
            ELSE j.obj
        END
    ) AS new_attrs
FROM game_data,
JSON_TABLE(
    attrs,
    '$[*]' COLUMNS(
        class VARCHAR(32) PATH '$.class',
        obj JSON PATH '$'
    )
) AS j
GROUP BY game_data.id; -- 按表的主键字段分组即可

注意事项 #

• JSON_MERGE_PATCH遵循JSON Merge Patch规范，若合并的属性在原对象中已存在会直接覆盖，嵌套对象会递归合并
• 执行更新操作前建议先通过SELECT语句验证路径匹配和合并结果，避免误改数据
• 上述语句自带过滤逻辑，无匹配元素的行不会被修改，不会破坏原数据

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