MySQL中修改JSON数组内嵌套路径元素的方法求助

第一步：修复JSON格式问题 #

你当前的roles数组里的元素是字符串类型的伪JSON（外层被双引号包裹，内部用单引号），MySQL的JSON函数无法直接解析内部结构。需要先把这些字符串转成合法的JSON对象：

UPDATE your_table
SET your_column = JSON_SET(
    your_column,
    '$.roles',
    JSON_ARRAYAGG(
        JSON_PARSE(JSON_UNQUOTE(REPLACE(role, "'", '"')))
    )
)
WHERE JSON_TYPE(your_column->'$.roles[0]') = 'STRING';

这段SQL会遍历roles数组的每个元素，把单引号替换成双引号，再解析成JSON对象，重新组装成合法的JSON数组。

第二步：修改指定角色的isActive字段 #

修复格式后，有两种可行方法修改目标项：

方法1：通过数组索引定位修改 #

先找到position为Manager的角色在数组中的索引，再用JSON_REPLACE替换对应位置的isActive：

-- 获取目标角色的索引
SELECT JSON_UNQUOTE(JSON_SEARCH(your_column, 'one', 'Manager', NULL, '$.roles[*].position')) AS role_index
FROM your_table;

-- 假设索引为$.roles[1]，执行更新
UPDATE your_table
SET your_column = JSON_REPLACE(
    your_column,
    '$.roles[1].isActive',
    JSON_OBJECT('active', 'no', 'transferredTo', 'Bank3')
)
WHERE JSON_UNQUOTE(your_column->'$.roles[1].position') = 'Manager';

方法2：使用JSON_TABLE动态定位（通用方案） #

如果数组索引不固定，用JSON_TABLE展开数组，自动匹配目标项并重组JSON：

UPDATE your_table t
JOIN (
    SELECT
        id, -- 替换为你的表主键字段
        JSON_ARRAYAGG(
            CASE WHEN j.position = 'Manager' THEN
                JSON_REPLACE(j.role, '$.isActive', JSON_OBJECT('active', 'no', 'transferredTo', 'Bank3'))
            ELSE j.role END
        ) AS updated_roles
    FROM your_table
    JOIN JSON_TABLE(
        your_column->'$.roles',
        '$[*]' COLUMNS (
            role JSON PATH '$',
            position VARCHAR(50) PATH '$.position'
        )
    ) j
    GROUP BY id
) j ON t.id = j.id
SET t.your_column = JSON_REPLACE(t.your_column, '$.roles', j.updated_roles);

该方法会自动遍历roles数组，找到position为Manager的项并修改其isActive，无需提前知道索引。

注意事项 #

• 替换your_table和your_column为实际表名与列名
• JSON_TABLE仅支持MySQL 8.0及以上版本
• 操作前建议备份数据，避免意外

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