如何在MySQL中用单条查询实现JSON数组元素求和并更新字段

场景说明 #

针对你使用MySQL 8.0.22版本，需要对JSON类型字段中嵌套的marks值累加求和的需求，可直接通过MySQL内置的JSON_TABLE函数实现单条语句操作，无需使用存储过程。

1. 仅查询求和结果（不修改原表） #

SELECT 
    t.id,
    t.students,
    SUM(j.marks) AS total_marks
FROM 
    替换为实际表名 t,
    JSON_TABLE(
        t.students->'$.students',
        '$[*]' COLUMNS (
            marks INT PATH '$.marks'
        )
    ) AS j
GROUP BY t.id, t.students;

执行后会直接返回包含计算后total_marks的结果集，和你预期的展示效果一致。

2. 更新表中total_marks字段（持久化修改） #

如果需要把计算结果直接写入表的total_marks字段，使用以下UPDATE语句即可：

UPDATE 替换为实际表名 t
INNER JOIN (
    SELECT 
        id,
        SUM(j.marks) AS sum_marks
    FROM 
        替换为实际表名 t,
        JSON_TABLE(
            t.students->'$.students',
            '$[*]' COLUMNS (
                marks INT PATH '$.marks'
            )
        ) AS j
    GROUP BY id
) AS calc ON t.id = calc.id
SET t.total_marks = calc.sum_marks;

注意事项 #

• 语句中的替换为实际表名需要修改为你自己的表名称
• 如果后续JSON结构调整，只要对应修改JSON_TABLE中的PATH路径即可适配
• 该语法仅兼容MySQL 8.0及以上版本，完全匹配你当前使用的8.0.22版本，无需额外配置

内容的提问来源于stack exchange，提问作者Manonandan S K