PostgreSQL中如何替换jsonb列内字符串的指定部分内容

你可以直接组合jsonb_set与字符串替换函数实现需求，不需要将整个jsonb转为文本修改再转回——后者存在误替换非目标字段内容的风险，优先使用精准路径匹配的写法。

方案1：固定结构精准替换（最安全，生产环境推荐） #

你的JSON结构固定包含large/small/medium/thumbnail四个顶层尺寸键，每个键下对应url字段，直接按路径提取目标url做替换后写回即可，完全不会影响其他字段内容：

UPDATE public.files 
SET formats = 
  jsonb_set(
    jsonb_set(
      jsonb_set(
        jsonb_set(
          formats,
          '{large,url}',
          to_jsonb(replace(formats #>> '{large,url}', 'bucket1', 'bucket2'))
        ),
        '{small,url}',
        to_jsonb(replace(formats #>> '{small,url}', 'bucket1', 'bucket2'))
      ),
      '{medium,url}',
      to_jsonb(replace(formats #>> '{medium,url}', 'bucket1', 'bucket2'))
    ),
    '{thumbnail,url}',
    to_jsonb(replace(formats #>> '{thumbnail,url}', 'bucket1', 'bucket2'))
  )
-- 加筛选条件只处理需要更新的记录，减少无效写入
WHERE formats::text LIKE '%bucket1%';

语句中用到的核心语法说明：

• #>> 操作符：按指定路径提取jsonb字段值，返回原生text类型，用于获取对应位置的原始url字符串
• replace()：执行固定字符串匹配替换，如果你需要更灵活的规则匹配（比如匹配不同前缀的bucket域名），可以替换为regexp_replace()函数
• to_jsonb()：将替换完成的text类型url转回jsonb格式的字符串值，满足jsonb_set的参数类型要求
• 嵌套jsonb_set：由于每次jsonb_set调用都会返回修改后的完整jsonb对象，嵌套调用即可依次完成四个尺寸对应url的修改

方案2：动态遍历替换（适配非固定结构场景） #

如果后续formats字段可能新增其他尺寸键，不想每次新增字段都调整SQL，可以通过遍历jsonb顶层键的方式批量替换所有子对象下的url字段：

UPDATE public.files f
SET formats = (
  SELECT jsonb_object_agg(
    key,
    CASE 
      WHEN value ? 'url' 
      THEN jsonb_set(value, '{url}', to_jsonb(replace(value->>'url', 'bucket1', 'bucket2')))
      ELSE value 
    END
  )
  FROM jsonb_each(f.formats)
)
WHERE formats::text LIKE '%bucket1%';

不推荐方案：整段jsonb转文本替换 #

-- 禁止在生产环境无校验使用
UPDATE public.files
SET formats = replace(formats::text, 'bucket1', 'bucket2')::jsonb
WHERE formats::text LIKE '%bucket1%';

风险提示：该写法会无差别替换整个JSON结构中所有出现bucket1的位置，如果hash、name等其他字段中恰好包含bucket1字符串，会被错误修改。除非你100%确定除url字段外其他位置永远不会出现待替换字符串，否则不要使用。

执行前校验建议 #

正式执行UPDATE前，建议先通过SELECT语句对比替换前后的字段值，确认结果符合预期：

SELECT 
  formats->'large'->>'url' AS old_large_url,
  replace(formats #>> '{large,url}', 'bucket1', 'bucket2') AS new_large_url,
  formats->'small'->>'url' AS old_small_url,
  replace(formats #>> '{small,url}', 'bucket1', 'bucket2') AS new_small_url
FROM public.files
WHERE formats::text LIKE '%bucket1%'
LIMIT 10;

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