如何在单条SELECT语句中替换JSON嵌套数组的多个值

不需要使用固定索引路径的JSON_REPLACE写法，在MySQL 8.0及以上版本，可以通过「拆解JSON数组-关联映射表-重组JSON」的逻辑实现动态替换，完全适配数组长度、元素值动态变化的场景。

实现步骤 #

• 用JSON_TABLE将values字段中roles数组的每个元素拆解为独立行，同时保留元素原顺序索引
• 关联roles映射表，通过id匹配拿到对应的角色名称
• 用JSON_ARRAYAGG按原顺序聚合角色名称为新数组，再包装为目标JSON对象

可直接运行的示例代码 #

假设存储values字段的主表名为业务表（请替换为你实际的表名），单条SELECT语句写法如下：

SELECT
    JSON_OBJECT(
        'roles',
        JSON_ARRAYAGG(r.role_name ORDER BY jt.array_index)
    ) AS final_result
FROM your_main_table m
-- 拆解JSON数组，保留原顺序
CROSS JOIN JSON_TABLE(
    m.`values`,
    '$.roles[*]' COLUMNS (
        role_id_str VARCHAR(32) PATH '$',
        array_index FOR ORDINALITY
    )
) jt
-- 关联角色表匹配名称，原JSON中id为字符串类型，做显式类型转换避免匹配失败
LEFT JOIN roles r ON r.id = CAST(jt.role_id_str AS UNSIGNED)
-- 按主表主键分组，支持批量查询多条记录的替换结果，单条查询也可保留
GROUP BY m.id;

注意事项 #

• FOR ORDINALITY关键字会自动生成数组元素的原始位置序号，聚合时按这个字段排序，可以保证替换后的角色名顺序和原数组中id的顺序完全一致，不会出现乱序
• 原JSON数组中存储的id是字符串格式（如"1"而非数值1），关联时显式转换为无符号整数和roles表的数值型id匹配，避免隐式类型转换导致的索引失效、匹配错误问题
• 如果数组中存在未在roles表维护映射的无效id，LEFT JOIN会返回null值，需要过滤无效id可以将LEFT JOIN改为INNER JOIN，需要保留空位可以搭配IFNULL(r.role_name, '未知角色')做兜底

如果你使用的是MySQL 5.7及以下版本，没有内置JSON_TABLE函数，无法通过单条SELECT语句优雅实现该动态替换逻辑，建议升级数据库版本，或在业务代码层做JSON拆解与映射匹配。

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