基于字符串替换更新PostgreSQL表中jsonb字段的URL值

针对你的需求——替换files表formats字段里所有嵌套对象的url值，把原域名部分my-digital-ocean-storage-1.fra1.digitaloceanspaces.com替换为my-digital-ocean-storage-2.fra1.cdn.digitaloceanspaces.com，提供两种可行方案：

方案1：已知所有顶层嵌套键的情况 #

如果你的formats字段里只有small和thumbnail这两个顶层键，可以直接用jsonb_set函数逐个更新每个键下的url：

UPDATE files
SET formats = jsonb_set(
    jsonb_set(
        formats,
        '{small,url}',
        to_jsonb(replace(
            (formats->'small'->>'url')::text,
            'https://my-digital-ocean-storage-1.fra1.digitaloceanspaces.com',
            'https://my-digital-ocean-storage-2.fra1.cdn.digitaloceanspaces.com'
        ))
    ),
    '{thumbnail,url}',
    to_jsonb(replace(
        (formats->'thumbnail'->>'url')::text,
        'https://my-digital-ocean-storage-1.fra1.digitaloceanspaces.com',
        'https://my-digital-ocean-storage-2.fra1.cdn.digitaloceanspaces.com'
    ))
);

代码说明： #

• formats->'small'->>'url'：提取small对象下的url字符串值
• replace()：完成域名部分的替换
• to_jsonb()：把替换后的字符串转回jsonb类型
• jsonb_set()：将新的url值写回原jsonb字段的对应位置

方案2：通用动态处理（适配任意顶层键） #

如果formats字段可能存在更多未知的顶层键（比如medium、large等），可以用jsonb_each遍历所有顶层键，再通过聚合函数重组更新后的jsonb对象：

UPDATE files f
SET formats = (
    SELECT jsonb_object_agg(
        key,
        jsonb_set(value, '{url}', to_jsonb(replace(
            (value->>'url')::text,
            'https://my-digital-ocean-storage-1.fra1.digitaloceanspaces.com',
            'https://my-digital-ocean-storage-2.fra1.cdn.digitaloceanspaces.com'
        )))
    )
    FROM jsonb_each(f.formats)
);

代码说明： #

• jsonb_each(f.formats)：把formats字段拆解为键值对的行集
• 对每个值（即嵌套的尺寸对象），替换其url值
• jsonb_object_agg()：将处理后的键值对重新聚合为完整的jsonb对象

注意事项 #

1. 先验证再更新：建议先执行SELECT语句预览更新结果，确认无误后再执行UPDATE：

   -- 方案1的验证语句
   SELECT 
       jsonb_set(
           jsonb_set(
               formats,
               '{small,url}',
               to_jsonb(replace((formats->'small'->>'url')::text, 'https://my-digital-ocean-storage-1.fra1.digitaloceanspaces.com', 'https://my-digital-ocean-storage-2.fra1.cdn.digitaloceanspaces.com'))
           ),
           '{thumbnail,url}',
           to_jsonb(replace((formats->'thumbnail'->>'url')::text, 'https://my-digital-ocean-storage-1.fra1.digitaloceanspaces.com', 'https://my-digital-ocean-storage-2.fra1.cdn.digitaloceanspaces.com'))
       ) AS updated_formats
   FROM files;
   
   -- 方案2的验证语句
   SELECT 
       (SELECT jsonb_object_agg(
           key,
           jsonb_set(value, '{url}', to_jsonb(replace((value->>'url')::text, 'https://my-digital-ocean-storage-1.fra1.digitaloceanspaces.com', 'https://my-digital-ocean-storage-2.fra1.cdn.digitaloceanspaces.com')))
       ) FROM jsonb_each(f.formats)) AS updated_formats
   FROM files f;
   

2. 数据备份：执行更新前建议备份files表，避免操作失误导致数据丢失
3. 分批更新：如果表中数据量很大，建议添加LIMIT或按条件分批更新，避免锁表影响业务

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