如何在MySQL中更新JSON数组对象内的languageCode值

我有一张applications表，其中Description列存储JSON数组格式的数据，示例记录如下：

[
    {"languageCode":"fr","description":"France","isDefault":true},
    {"languageCode":"cs","description":"Czech","isDefault":false},
    {"languageCode":"it-IT","description":"Italian","isDefault":false}
]

和

[
    {"languageCode":"cs","description":"Czech","isDefault":false},
    {"languageCode":"it-IT","description":"Italian","isDefault":false},
    {"languageCode":"fr-FR","description":"France","isDefault":true}
]

需要将所有记录中languageCode为"fr"的修改为"fr-FR"，"cs"修改为"cs-CZ"，期望结果如下：

[
    {"languageCode":"fr-FR","description":"France","isDefault":true},
    {"languageCode":"cs-CZ","description":"Czech","isDefault":false},
    {"languageCode":"it-IT","description":"Italian","isDefault":false}
]

和

[
    {"languageCode":"cs-CZ","description":"Czech","isDefault":false},
    {"languageCode":"it-IT","description":"Italian","isDefault":false},
    {"languageCode":"fr-FR","description":"France","isDefault":true}
]

我已经通过嵌套REPLACE语句实现了需求：

UPDATE applications
SET Description = REPLACE ( REPLACE ( Description, '"languageCode":"fr"', '"languageCode":"fr-FR"'), '"languageCode":"cs"', '"languageCode":"cs-CZ"')

但希望使用更规范的JSON相关方法来完成这个更新操作。

字符串REPLACE虽然简单，但存在误替换风险（比如description字段中恰好包含匹配文本的情况）。以下是针对主流数据库的安全JSON处理方案：

SQL Server #

使用OPENJSON拆分JSON数组，修改字段后再用FOR JSON PATH重组：

UPDATE a
SET Description = (
    SELECT 
        CASE 
            WHEN j.languageCode = 'fr' THEN 'fr-FR'
            WHEN j.languageCode = 'cs' THEN 'cs-CZ'
            ELSE j.languageCode
        END AS languageCode,
        j.description,
        j.isDefault
    FROM OPENJSON(a.Description)
    WITH (
        languageCode NVARCHAR(10),
        description NVARCHAR(100),
        isDefault BIT
    ) j
    FOR JSON PATH
)
FROM applications a

MySQL #

借助JSON_TABLE解析数组，JSON_OBJECT重建对象，JSON_ARRAYAGG聚合为数组：

UPDATE applications a
JOIN (
    SELECT 
        id, -- 替换为你的表主键字段
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'languageCode', CASE WHEN j.languageCode = 'fr' THEN 'fr-FR' WHEN j.languageCode = 'cs' THEN 'cs-CZ' ELSE j.languageCode END,
                'description', j.description,
                'isDefault', j.isDefault
            )
        ) AS new_description
    FROM applications,
         JSON_TABLE(
             Description,
             '$[*]' COLUMNS (
                 languageCode VARCHAR(10) PATH '$.languageCode',
                 description VARCHAR(100) PATH '$.description',
                 isDefault BOOLEAN PATH '$.isDefault'
             )
         ) j
    GROUP BY id
) b ON a.id = b.id
SET a.Description = b.new_description;

PostgreSQL #

利用jsonb_array_elements拆分数组，jsonb_build_object重建对象，jsonb_agg聚合回数组：

UPDATE applications a
SET Description = (
    SELECT jsonb_agg(
        jsonb_build_object(
            'languageCode', CASE WHEN j.language_code = 'fr' THEN 'fr-FR' WHEN j.language_code = 'cs' THEN 'cs-CZ' ELSE j.language_code END,
            'description', j.description,
            'isDefault', j.is_default
        )
    )
    FROM jsonb_array_elements(a.Description::jsonb) j,
         jsonb_to_record(j) AS x(language_code text, description text, is_default boolean)
)::json; -- 如果原字段是json类型，需要转换回来

• 字符串REPLACE：实现成本低，但容错性差，易出现误替换
• JSON函数方案：严格针对JSON结构中的指定字段修改，避免误操作，适合复杂JSON场景，扩展性更强

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