PostgreSQL 12.x JSONB字段迁移：将嵌套对象替换为key值

针对你提到的需求——替换typename表data列中所有包含key和source键（且source为INPUT）的嵌套对象为其key对应值，可通过以下步骤实现：

1. 创建递归处理函数 #

由于JSON结构不固定，需要递归遍历所有层级的对象和数组，匹配并替换符合条件的节点。创建PL/pgSQL函数：

CREATE OR REPLACE FUNCTION replace_input_objects(jsonb_data jsonb)
RETURNS jsonb AS $$
DECLARE
    temp_json jsonb;
    keys text[];
    key text;
    value jsonb;
BEGIN
    temp_json := jsonb_data;

    -- 遍历当前层级所有键
    keys := ARRAY(SELECT jsonb_object_keys(temp_json));
    FOREACH key IN ARRAY keys LOOP
        value := temp_json -> key;

        -- 处理对象类型节点
        IF jsonb_typeof(value) = 'object' THEN
            -- 检查是否符合替换条件：存在key和source，且source为INPUT
            IF value ? 'key' AND value ? 'source' AND (value ->> 'source') = 'INPUT' THEN
                -- 替换为key字段的值
                temp_json := temp_json || jsonb_build_object(key, value -> 'key');
            ELSE
                -- 递归处理嵌套对象
                temp_json := temp_json || jsonb_build_object(key, replace_input_objects(value));
            END IF;
        -- 处理数组类型节点
        ELSIF jsonb_typeof(value) = 'array' THEN
            temp_json := temp_json || jsonb_build_object(key, (
                SELECT jsonb_agg(replace_input_objects(elem))
                FROM jsonb_array_elements(value) elem
            ));
        END IF;
    END LOOP;

    RETURN temp_json;
END;
$$ LANGUAGE plpgsql;

2. 执行更新操作 #

使用上述函数批量更新符合条件的数据行：

-- 先验证处理效果（可选，避免误操作）
SELECT data, replace_input_objects(data) AS updated_data
FROM typename
WHERE jsonb_path_exists(data, '$.** ? (@.key && @.source == "INPUT")')
LIMIT 5;

-- 确认无误后执行更新
UPDATE typename
SET data = replace_input_objects(data)
WHERE jsonb_path_exists(data, '$.** ? (@.key && @.source == "INPUT")');

注意事项 #

• 如果你的data列是json类型而非jsonb，需在函数调用时进行类型转换，例如replace_input_objects(data::jsonb)::json，建议长期使用jsonb类型以获得更好的性能和操作支持。
• 执行更新前务必做好数据备份，或先通过SELECT验证处理结果，避免数据丢失或错误。

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