服务器迁移：如何递归查找并原地更新JSON任意层级指定键值对？

场景背景 #

服务器迁移时需完成以下数据更新：

• media_items表的asset字段：将其中的url值替换为新服务器地址
• document_revisions表的data字段：替换JSON结构任意层级下的url（指向新服务器）和originalUrl（指向AWS S3存储桶）值

当前痛点：采用字符串全局替换时效率极低（仅150条数据耗时超2小时）；media_items可通过JSONB_SET高效更新，但document_revisions的目标键分布在JSON任意层级，无法直接定位更新。

问题解答 #

1. 能否原地更新数组任意层级的指定JSON键值？ #

可以。通过PostgreSQL的递归CTE或自定义PL/pgSQL函数，能够遍历JSONB结构的所有层级（包括嵌套对象、数组），识别目标键后替换对应值，最后重组为完整的JSONB数据实现原地更新。

2. 能否提升字符串替换的速度？ #

可以，核心是避免无差别字符串操作，优化方向包括：

• 过滤先行：先通过WHERE条件筛选出包含旧URL的行，避免全表更新
• 用JSONB操作替代字符串操作：减少JSON解析、拼接的额外开销，利用数据库对JSONB的原生优化
• 批量更新：采用批量事务而非单条更新，降低事务提交的开销

3. 最优实现方案 #

针对两张表的不同特点，分场景处理：

（1）media_items表：精准定位更新

利用JSONB_SET直接定位asset->url路径，高效完成更新：

UPDATE media_items
SET asset = jsonb_set(
  asset,
  '{url}',
  to_jsonb(replace((asset->>'url'), 'https://old-server.example.com/', 'https://new-server.example.com/'))
)
WHERE asset->>'url' LIKE 'https://old-server.example.com/%';

（2）document_revisions表：递归遍历替换

自定义递归函数遍历JSON所有层级，替换目标键值后写回表中：

-- 创建递归替换函数
CREATE OR REPLACE FUNCTION replace_jsonb_target_keys(
  jsonb_val jsonb,
  old_url_prefix text,
  new_server_url text,
  s3_bucket_url text
) RETURNS jsonb AS $$
BEGIN
  IF jsonb_val IS NULL THEN
    RETURN NULL;
  END IF;

  -- 处理JSON对象
  IF jsonb_typeof(jsonb_val) = 'object' THEN
    RETURN (
      SELECT jsonb_object_agg(
        key,
        CASE
          WHEN key = 'url' THEN to_jsonb(replace(jsonb_val->>key, old_url_prefix, new_server_url))
          WHEN key = 'originalUrl' THEN to_jsonb(replace(jsonb_val->>key, old_url_prefix, s3_bucket_url))
          ELSE replace_jsonb_target_keys(jsonb_val->key, old_url_prefix, new_server_url, s3_bucket_url)
        END
      )
      FROM jsonb_each(jsonb_val)
    );
  -- 处理JSON数组
  ELSIF jsonb_typeof(jsonb_val) = 'array' THEN
    RETURN (
      SELECT jsonb_agg(replace_jsonb_target_keys(element, old_url_prefix, new_server_url, s3_bucket_url))
      FROM jsonb_array_elements(jsonb_val) AS element
    );
  -- 非对象/数组类型直接返回
  ELSE
    RETURN jsonb_val;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- 执行更新
UPDATE document_revisions
SET data = replace_jsonb_target_keys(
  data,
  'https://old-server.example.com/',
  'https://new-server.example.com/',
  'https://your-s3-bucket.example.com/'
)
WHERE data::text LIKE '%https://old-server.example.com/%';

方案优势

• 递归函数精准遍历所有JSON节点，仅替换目标键，避免字符串替换的误匹配风险
• 利用PostgreSQL对JSONB的原生优化，处理效率远高于字符串全局替换
• 批量更新减少事务开销，大幅缩短处理时间

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