PostgreSQL中如何基于分隔符替换JSON内的完整字符串？

问题分析 #

你当前用简单replace只能替换部分内容，根源是旧URL包含可变路径和?id=参数，固定字符串无法匹配完整目标。必须用正则表达式匹配精准定位符合格式的完整旧URL，再完成替换。

解决方案 #

使用PostgreSQL的regexp_replace函数结合正则表达式，配合JSON字段的类型转换，实现全局批量替换。

核心正则逻辑 #

匹配旧URL的正则表达式：

"https://my\\.oldserver\\.com/api/v1/images/[^"?,]+\\?id=[^"?,]+"

• https://my\\.oldserver\\.com/api/v1/images/：匹配旧URL固定开头（.需转义）
• [^"?,]+：匹配路径部分，直到遇到"、?或,停止
• \\?id=：匹配?id=参数（?需转义）
• [^"?,]+：匹配id参数值，直到遇到结尾符
• "：匹配URL结尾的双引号（JSON中URL通常用双引号包裹）

完整SQL示例 #

替换data字段中所有符合格式的旧URL（包括originalUrl和url字段）：

UPDATE document_revisions dr
SET data = (
  regexp_replace(
    dr.data::text,
    '"https://my\\.oldserver\\.com/api/v1/images/[^"?,]+\\?id=[^"?,]+"',
    '"https://my.newserver.com/2022/01/18/929009ee-cda6-4227-83e4-80fc954730b6.jpeg"',
    'g' -- 全局替换，匹配所有符合条件的URL
  )
)::jsonb;

针对特定键的替换（可选） #

如果仅需替换url字段的URL，调整正则匹配键名：

UPDATE document_revisions dr
SET data = (
  regexp_replace(
    dr.data::text,
    '"url":\s*"https://my\\.oldserver\\.com/api/v1/images/[^"?,]+\\?id=[^"?,]+"',
    '"url": "https://my.newserver.com/2022/01/18/929009ee-cda6-4227-83e4-80fc954730b6.jpeg"',
    'g'
  )
)::jsonb;

注意事项 #

• 先测试再执行更新：先用SELECT验证替换结果，避免误操作：

  SELECT 
    dr.data::text AS original_text,
    regexp_replace(
      dr.data::text,
      '"https://my\\.oldserver\\.com/api/v1/images/[^"?,]+\\?id=[^"?,]+"',
      '"https://my.newserver.com/2022/01/18/929009ee-cda6-4227-83e4-80fc954730b6.jpeg"',
      'g'
    ) AS replaced_text
  FROM document_revisions dr
  LIMIT 10;
  

• JSON类型转换：确保data字段为jsonb或json类型，转text处理后再转回原类型。
• 动态生成新URL（可选）：若需根据旧URL的id参数生成新URL，可提取id后拼接：

  UPDATE document_revisions dr
  SET data = (
    SELECT regexp_replace(
      dr.data::text,
      '"https://my\\.oldserver\\.com/api/v1/images/[^"?,]+\\?id=([^"?,]+)"',
      format('"https://my.newserver.com/%s.jpeg"', '\1'),
      'g'
    )
  )::jsonb;
  

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