如何用MySQL的JSON_SET修改JSON数组指定ID元素的seen字段

方法1：使用JSON_TABLE展开并重新聚合数组（适合批量/多匹配场景） #

这种方法通过将JSON数组转换为关系表结构，修改指定字段后重新聚合为JSON数组，再更新回原字段。适用于需要更新多个匹配id的元素，兼容MySQL 8.0.14及以上版本：

-- 假设要更新的目标id为'11888572'
WITH updated_scores AS (
    SELECT
        c.GameID,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'id', s.id,
                'seen', CASE WHEN s.id = '11888572' THEN TRUE ELSE s.seen END,
                'score', s.score
            )
        ) AS new_scores
    FROM completedgames c
    JOIN JSON_TABLE(
        c.gameresults->'$.scores',
        '$[*]' COLUMNS(
            id VARCHAR(20) PATH '$.id',
            seen BOOLEAN PATH '$.seen',
            score INT PATH '$.score'
        )
    ) s
    -- 可添加WHERE条件限定特定GameID，缩小更新范围
    -- WHERE c.GameID = 'acaa2a99-a24c'
    GROUP BY c.GameID
)
UPDATE completedgames c
JOIN updated_scores us ON c.GameID = us.GameID
SET c.gameresults = JSON_SET(c.gameresults, '$.scores', us.new_scores);

说明： #

• JSON_TABLE 将gameresults中的scores数组解析为行数据，获取每个元素的id、seen、score字段
• CASE 语句判断当前元素的id是否为目标值，是则将seen设为true，否则保留原数值
• JSON_ARRAYAGG 将修改后的行数据重新聚合为JSON数组
• 最后通过JSON_SET将新数组替换回原字段的scores节点

方法2：使用JSON_SEARCH定位路径后更新（适合单个匹配场景） #

如果scores数组里的id是唯一的，可以通过JSON_SEARCH找到目标元素的路径，再直接更新seen字段：

-- 设置目标id变量
SET @target_id = '11888572';
-- 设置目标GameID（可选，若需更新特定游戏）
SET @target_game_id = 'acaa2a99-a24c';

-- 查找目标元素的seen字段路径
SELECT @update_path := REPLACE(
    JSON_SEARCH(gameresults, 'one', @target_id, NULL, '$.scores[*].id'),
    '.id',
    '.seen'
)
FROM completedgames
WHERE GameID = @target_game_id;

-- 执行更新
UPDATE completedgames
SET gameresults = JSON_SET(gameresults, @update_path, TRUE)
WHERE GameID = @target_game_id;

说明： #

• JSON_SEARCH 查找匹配id的元素路径（返回格式如$.scores[0].id）
• REPLACE 将路径中的.id替换为.seen，得到目标字段的路径（如$.scores[0].seen）
• JSON_SET 利用该路径直接更新seen字段为true
• 若数组中存在多个相同id的元素，将JSON_SEARCH的第二个参数'one'改为'all'，并结合存储过程或脚本循环处理返回的路径数组

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