MySQL如何计算JSON数组指定属性值并更新到同记录其他列

方案1：MySQL 8.0+ 原生函数实现（推荐，无额外依赖） #

核心逻辑是用JSON_TABLE把存储的JSON数组拆分为多行单对象，对每个对象的age值做乘法运算后，再重新拼接为标准JSON数组，直接执行UPDATE即可完成写入。

• 假设表结构参考：
  表名：person_list，原JSON存储列：origin_json_col，待写入处理结果的目标列：target_json_col，主键列：record_id
• 对应更新SQL示例：

UPDATE person_list
SET target_json_col = (
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'name', jt.name,
            'age', jt.age * 2
        )
    )
    FROM JSON_TABLE(
        origin_json_col,
        '$[*]' COLUMNS (
            name VARCHAR(255) PATH '$.name',
            age DECIMAL(10,2) PATH '$.age'
        )
    ) AS jt
)
WHERE record_id = 1; -- 替换成实际要更新的记录匹配条件

注意：如果乘数不是固定2，直接修改jt.age * 2里的乘数值即可，比如要乘3就改成jt.age * 3

方案2：MySQL 5.7版本兼容方案 #

MySQL 5.7没有内置JSON_TABLE函数，可根据业务场景选以下两种实现方式：

• 数组长度固定场景：如果JSON数组的元素个数固定，可以直接按数组下标逐个取值修改后拼接，比如示例中数组固定2个元素的写法：

UPDATE person_list
SET target_json_col = JSON_ARRAY(
    JSON_OBJECT('name', JSON_UNQUOTE(JSON_EXTRACT(origin_json_col, '$[0].name')), 'age', JSON_EXTRACT(origin_json_col, '$[0].age') *2),
    JSON_OBJECT('name', JSON_UNQUOTE(JSON_EXTRACT(origin_json_col, '$[1].name')), 'age', JSON_EXTRACT(origin_json_col, '$[1].age') *2)
)
WHERE record_id =1;

• 数组长度不固定场景：提前创建一张存0~N连续整数的辅助表help_tbl，num列存连续序号，最大值不超过JSON数组的最大可能长度，用下标关联的方式拆分数组再聚合，示例SQL：

-- 辅助表只需创建一次
CREATE TABLE IF NOT EXISTS help_tbl (num INT PRIMARY KEY);
-- 插入0到100的连续数，可按需调整最大值
INSERT INTO help_tbl(num) 
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 -- 按需求补全到足够大的数值
;

-- 执行更新
UPDATE person_list pl
SET target_json_col = (
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'name', JSON_UNQUOTE(JSON_EXTRACT(pl.origin_json_col, CONCAT('$[', ht.num, '].name'))),
            'age', JSON_EXTRACT(pl.origin_json_col, CONCAT('$[', ht.num, '].age')) *2
        )
    )
    FROM help_tbl ht
    WHERE ht.num < JSON_LENGTH(pl.origin_json_col)
)
WHERE pl.record_id =1;

方案3：应用层处理（通用性最强，不受MySQL版本限制） #

如果不想写复杂的JSON函数SQL，可以在业务代码里读取原列的JSON值，反序列化为对象列表，遍历把每个对象的age乘2后再序列化为JSON字符串，回写到目标列即可。以Python为例的核心逻辑参考：

import json
# 伪代码，替换为实际的数据库操作逻辑
record = db.query("SELECT origin_json_col FROM person_list WHERE record_id = 1")
origin_list = json.loads(record["origin_json_col"])
for item in origin_list:
    item["age"] = item["age"] * 2
processed_json = json.dumps(origin_list)
# 执行更新回写
db.execute("UPDATE person_list SET target_json_col = %s WHERE record_id =1", (processed_json,))

执行更新前建议先SELECT预览计算结果，确认数值计算正确、JSON格式合法后再执行UPDATE操作，避免产生脏数据。

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