如何编写PostgreSQL存储函数拆分匹配与不匹配的jsonb数组

以下是补充核心逻辑后的完整存储函数：

CREATE OR REPLACE FUNCTION get_matched_array(
    details jsonb,
    provider_details jsonb[]
) RETURNS TABLE (matched jsonb[], unmatched jsonb[]) AS $$
DECLARE
    matched jsonb[] := '{}'::jsonb[]; -- 初始化空数组
    unmatched jsonb[] := '{}'::jsonb[];
    target_category text;
    item jsonb;
BEGIN
    -- 提取details中的category值，转小写用于不区分大小写匹配
    target_category := lower(details ->> 'category');
    
    -- 遍历provider_details数组中的每个元素
    FOREACH item IN ARRAY provider_details LOOP
        -- 比较当前元素的category_type（转小写）与目标category是否一致
        IF lower(item ->> 'category_type') = target_category THEN
            matched := array_append(matched, item);
        ELSE
            unmatched := array_append(unmatched, item);
        END IF;
    END LOOP;
    
    RETURN QUERY SELECT matched, unmatched;
END;
$$ LANGUAGE plpgsql;

核心逻辑说明 #

• 初始化数组：提前将matched和unmatched初始化为空JSONB数组，避免空值异常
• 提取目标值：从details中取出category字段并转为小写，适配示例中wood与Wood的不区分大小写匹配场景
• 遍历数组元素：使用FOREACH循环遍历provider_details数组的每个JSONB对象
• 分类存储：将匹配的对象追加到matched数组，不匹配的追加到unmatched数组

示例调用与结果 #

执行以下调用语句：

SELECT get_matched_array(
    '{"category": "wood"}'::jsonb,
    ARRAY[
        '{"category_type": "Wood","category_type_id": 2}'::jsonb,
        '{"category_type": "Iron","category_type_id": 3}'::jsonb
    ]
);

预期返回结果：

matched                                   | unmatched
------------------------------------------|------------------------------------------
[{"category_type":"Wood","category_type_id":2}] | [{"category_type":"Iron","category_type_id":3}]

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