PostgreSQL 9.5+修改JSON数组字段的最优方案咨询

方法一：CASE WHEN 基础实现 #

适合映射规则较少的临时场景，直接对数组元素逐个判断替换，再重组JSON结构。

示例SQL：

UPDATE X
SET config = json_build_object(
    'frequency',
    (SELECT json_agg(
        CASE elem
            WHEN 'MONTHLY' THEN 'P1M'
            WHEN 'WEEKLY' THEN 'P1W'
            -- 按需添加更多映射规则
            ELSE elem -- 未匹配的元素保留原值
        END
    ) FROM json_array_elements_text(config->'frequent') AS elem)
    -- 如果原config有其他键，手动添加保留，比如：
    -- , 'other_key', config->'other_key'
)
WHERE config ? 'frequent'; -- 仅更新包含frequent键的行

方法二：JSON映射表实现（更优方案） #

当映射规则较多或需要长期维护时，用预定义的JSON映射对象替换，代码更简洁易扩展，还能自动保留原JSON的其他键。

示例SQL：

-- 定义映射规则，后续修改只需更新这个JSON对象
WITH mapping AS (
    SELECT '{"MONTHLY": "P1M", "WEEKLY": "P1W"}'::json AS map
)
UPDATE X
SET config = json_build_object(
    'frequency',
    (SELECT json_agg(
        COALESCE(map->>elem, elem) -- 有映射值就替换，无则保留原值
    ) FROM json_array_elements_text(config->'frequent') AS elem, mapping)
) || config - 'frequent' -- 移除原frequent键，合并新的frequency键，自动保留其他键
WHERE config ? 'frequent';

如果你的config字段是jsonb类型（推荐，操作性能更好），可以改成jsonb版本：

WITH mapping AS (
    SELECT '{"MONTHLY": "P1M", "WEEKLY": "P1W"}'::jsonb AS map
)
UPDATE X
SET config = jsonb_build_object(
    'frequency',
    (SELECT jsonb_agg(
        COALESCE(map->>elem, elem)
    ) FROM jsonb_array_elements_text(config->'frequent') AS elem, mapping)
) || (config - 'frequent')
WHERE config ? 'frequent';

方案对比 #

• CASE WHEN：代码直观，但映射规则多的时候会很冗长，新增规则必须修改CASE分支，适合临时小需求。
• JSON映射表：映射规则集中管理，修改方便，还能自动保留原JSON的其他键，不用手动列举，扩展性和效率都更好，适合长期维护的场景。

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