Postgres SQL中替换子串并按UUID规则迁移JSON字段的方案咨询

实现思路 #

• 优先使用Postgres原生jsonb类型操作函数处理JSON结构，比正则替换更稳妥，可避免意外修改到其他字段内容
• 利用Postgres原生UUID类型做合法性校验，比手写正则的准确率更高
• 逐行处理数据时先校验params数组内每个value的格式，再对应生成新的结构

具体实现代码 #

1. 定义UUID校验函数 #

通过尝试将字符串转换为UUID类型的方式判断格式合法性，兼容性比正则更好：

CREATE OR REPLACE FUNCTION is_valid_uuid(val text)
RETURNS boolean AS $$
BEGIN
    RETURN (val::uuid IS NOT NULL);
EXCEPTION
    WHEN invalid_text_representation THEN
        RETURN false;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

2. 转换效果预验证SQL #

执行更新前先运行查询验证转换结果是否符合预期，把your_table替换为实际表名、original_col替换为存储JSON的text列名即可：

SELECT 
    original_col AS 原始数据,
    jsonb_set(
        original_col::jsonb,
        ARRAY['params', elem_index::text, 'value'],
        CASE 
            WHEN is_valid_uuid(elem->>'value') 
                THEN jsonb_build_object('id', elem->>'value', 'path', '/hardcoded/string')
            ELSE jsonb_build_object('value', elem->>'value', 'path', '')
        END
    )::text AS 转换后数据
FROM 
    your_table,
    jsonb_array_elements(original_col::jsonb->'params') WITH ORDINALITY arr(elem, elem_index);

3. 批量更新SQL #

确认转换逻辑正确后执行更新，建议用事务包裹，出错可回滚：

BEGIN;
-- 先备份全量数据，防止操作失误
CREATE TABLE your_table_backup AS SELECT * FROM your_table;

-- 执行更新
UPDATE your_table
SET original_col = (
    SELECT jsonb_set(
        original_col::jsonb,
        ARRAY['params', elem_index::text, 'value'],
        CASE 
            WHEN is_valid_uuid(elem->>'value') 
                THEN jsonb_build_object('id', elem->>'value', 'path', '/hardcoded/string')
            ELSE jsonb_build_object('value', elem->>'value', 'path', '')
        END
    )::text
    FROM jsonb_array_elements(original_col::jsonb->'params') WITH ORDINALITY arr(elem, elem_index)
);

-- 验证更新结果无误后再执行提交，出错则执行ROLLBACK回滚
-- COMMIT;
-- ROLLBACK;

注意事项 #

• 如果params字段可能为空、不存在，可在更新语句中加过滤条件WHERE original_col::jsonb ? 'params' AND jsonb_array_length(original_col::jsonb->'params') > 0跳过无效数据
• 数据量超过10万行的场景建议分批处理，避免锁表时间过长影响业务
• 生产环境操作前必须先在测试环境完整验证转换逻辑和数据正确性

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