Postgres中移除嵌套jsonb数组内指定file-id的对象

场景1：仅处理items下动态键对应的数组（无深层嵌套） #

如果只需要移除items下所有动态键对应数组中file-id为file-1的对象，不需要处理更深层的嵌套，可以直接用以下UPDATE语句：

UPDATE your_table
SET data = jsonb_set(
    data,
    '{items}',
    (
        -- 把items的动态键值对拆解开，处理每个数组后重新聚合
        SELECT jsonb_object_agg(item_key, filtered_array)
        FROM jsonb_each(data->'items') AS items(item_key, item_array)
        CROSS JOIN LATERAL (
            -- 过滤数组中file-id为file-1的对象，重新聚合数组
            SELECT jsonb_agg(obj) AS filtered_array
            FROM jsonb_array_elements(item_array) AS obj
            WHERE obj->>'file-id' != 'file-1'
        ) AS filtered_arrays
    )
)
-- 只更新确实包含目标对象的行，提升执行效率
WHERE data @> '{"items": {"": [{"file-id": "file-1"}]}}'::jsonb;

代码说明： #

• jsonb_each(data->'items')：将items下的动态键值对拆分为多行记录，item_key是动态生成的键名，item_array是对应的值（数组）
• 内层jsonb_array_elements(item_array)：拆分每个数组中的对象，筛选掉file-id等于file-1的条目后，用jsonb_agg重新组合成数组
• jsonb_object_agg：将处理后的键和数组重新拼接为items对象
• jsonb_set：把原JSON中的items字段替换为处理后的新对象
• WHERE子句：避免对不包含目标对象的行执行无意义更新

场景2：处理任意深度的嵌套JSON（递归移除） #

如果JSON结构存在多层嵌套（比如数组中的对象还包含子数组/子对象，且其中也有需要移除的file-id对象），可以创建一个递归函数来遍历整个JSON结构，统一过滤目标对象：

第一步：创建递归过滤函数 #

CREATE OR REPLACE FUNCTION filter_json_objects(json_data jsonb, exclude_file_id text)
RETURNS jsonb AS $$
BEGIN
    -- 处理数组：过滤目标对象，递归处理数组内每个元素
    IF jsonb_typeof(json_data) = 'array' THEN
        RETURN (
            SELECT jsonb_agg(
                CASE WHEN jsonb_typeof(elem) IN ('object', 'array')
                     THEN filter_json_objects(elem, exclude_file_id)
                     ELSE elem
                END
            )
            FROM jsonb_array_elements(json_data) AS elem
            WHERE elem->>'file-id' != exclude_file_id OR elem->>'file-id' IS NULL
        );
    -- 处理对象：递归处理每个键对应的值
    ELSIF jsonb_typeof(json_data) = 'object' THEN
        RETURN (
            SELECT jsonb_object_agg(key, filter_json_objects(value, exclude_file_id))
            FROM jsonb_each(json_data) AS obj(key, value)
        );
    -- 非数组/对象类型直接返回
    ELSE
        RETURN json_data;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

第二步：执行UPDATE语句 #

UPDATE your_table
SET data = filter_json_objects(data, 'file-1')
-- 匹配所有包含目标file-id的行
WHERE data @> '{"file-id": "file-1"}'::jsonb 
   OR data @> '{"items": {"": [{"file-id": "file-1"}]}}'::jsonb;

代码说明： #

• 函数会递归遍历JSON的每个节点：遇到数组就过滤掉file-id匹配的对象，同时递归处理数组内的每个元素；遇到对象则递归处理每个键对应的值
• 执行UPDATE时，直接调用函数处理整个JSON字段，实现全深度的对象移除

注意事项 #

1. 确保你的JSON字段是jsonb类型（PostgreSQL中json类型不支持修改操作，若字段是json，可临时转为jsonb处理，比如data::jsonb）
2. 执行UPDATE前，建议先用SELECT验证结果：

   SELECT filter_json_objects(data, 'file-1') FROM your_table WHERE id = 1;
   

3. 如果不需要处理全深度嵌套，优先使用场景1的语句，性能更优

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