PostgreSQL中合并JSONB数组：更新已有项并添加新项的实现方案

你的核心需求是对JSONB数组按fav唯一键实现新增项+更新已有项的合并逻辑，之前的函数因为错误拆解了JSON对象结构导致无法工作，下面给你两种可行的解决方案，同时解释思路：

问题分析 #

你原来的函数使用了jsonb_each和json_each，这会把每个JSON对象拆成键值对的行，完全偏离了按fav字段匹配整个对象的需求，自然无法正确合并。正确的思路应该是：把JSON数组展开为行级对象，用关系型数据库的匹配逻辑处理去重和更新，再重新聚合为数组。

方案一：使用NOT IN筛选未覆盖项（简洁版） #

这个函数逻辑清晰，适合中小规模的JSON数组：

CREATE OR REPLACE FUNCTION public.json_array_merge(data1 jsonb, merge_data jsonb)
RETURNS jsonb
IMMUTABLE
LANGUAGE sql
AS $$
SELECT jsonb_agg(item ORDER BY (item->>'fav')::int)  -- 按fav排序，可选，匹配你的示例结果
FROM (
    -- 第一步：保留所有传入的merge_data项（更新已有项+新增项）
    SELECT elem AS item
    FROM jsonb_array_elements(merge_data) elem
    UNION ALL
    -- 第二步：从原数据中筛选出未被merge_data覆盖的项（fav不在merge_data中）
    SELECT elem AS item
    FROM jsonb_array_elements(data1) elem
    WHERE (elem->>'fav') NOT IN (
        SELECT (elem->>'fav')
        FROM jsonb_array_elements(merge_data) elem
    )
) combined;
$$;

测试示例 #

执行以下语句验证：

SELECT json_array_merge(
    '[{"fav": 1, "is_active": true, "date": "1999-00-00 11:07:05.710000"}, {"fav": 2, "is_active": true, "date": "1998-00-00 11:07:05.710000"}]'::jsonb,
    '[{"fav": 3, "is_active": true, "date": "2019-00-00 11:07:05.710000"}, {"fav": 1, "is_active": false, "date": "2020-00-00 11:07:05.710000"}]'::jsonb
);

会得到你预期的结果：

[{"fav": 1, "is_active": false, "date": "2020-00-00 11:07:05.710000"}, {"fav": 2, "is_active": true, "date": "1998-00-00 11:07:05.710000"}, {"fav": 3, "is_active": true, "date": "2019-00-00 11:07:05.710000"}]

方案二：使用LEFT JOIN筛选未覆盖项（高性能版） #

如果你的JSON数组规模较大，LEFT JOIN的性能通常优于NOT IN，可以用这个版本：

CREATE OR REPLACE FUNCTION public.json_array_merge(data1 jsonb, merge_data jsonb)
RETURNS jsonb
IMMUTABLE
LANGUAGE sql
AS $$
SELECT jsonb_agg(item ORDER BY (item->>'fav')::int)
FROM (
    SELECT elem AS item
    FROM jsonb_array_elements(merge_data) elem
    UNION ALL
    SELECT d.elem AS item
    FROM jsonb_array_elements(data1) d
    -- 左关联merge_data，筛选出merge_data中没有匹配fav的原数据项
    LEFT JOIN jsonb_array_elements(merge_data) m 
        ON (d.elem->>'fav') = (m.elem->>'fav')
    WHERE m.elem IS NULL
) combined;
$$;

额外说明 #

1. JSON结构是否需要调整？
   不需要，当前的结构完全满足需求，只要fav是唯一标识即可，无需修改JSON结构。
2. 为什么要展开为集合处理？
   PostgreSQL对JSON的原生操作适合简单场景，但涉及到复杂的匹配、去重逻辑时，将JSON数组展开为行级对象（集合），利用关系型数据库的JOIN、UNION等操作会更灵活、高效，处理完成后再聚合回JSONB数组是标准的处理方式。

内容的提问来源于stack exchange，提问作者Хамидилло Мамытов