PostgreSQL如何对数组进行内联过滤？

云上老周

2026-8-3

数组元素过滤拆分的解决方案

针对你需要从数组中过滤出特定元素并拆分为新数组的需求，以下是PostgreSQL中的可行方案：

原始问题回顾

你的数据表结构：

id	bar	foos
1	3	{'A Young Foo', 'Lil Young', 'An Old Foo'}
2	6	{'Another Old Foo', 'A normal Foo'}

期望得到的结果：

id	bar	young_foos	old_foos
1	3	{'A Young Foo', 'Lil Young'}	{'An Old Foo'}
2	6	{}	{'Another Old Foo'}

你之前尝试的FILTER语法无法运行，因为FILTER是聚合函数的专用子句，不能直接作用于数组列本身。

方法一：内联数组构造查询

这是最简洁的写法，直接通过ARRAY()构造器配合unnest()拆分并过滤数组：

SELECT
    id,
    bar,
    ARRAY(SELECT unnest(foos) WHERE unnest(foos) LIKE '%Young%') AS young_foos,
    ARRAY(SELECT unnest(foos) WHERE unnest(foos) LIKE '%Old%') AS old_foos
FROM your_table;

unnest(foos)将数组拆分为单独的行元素
过滤符合LIKE条件的元素后，用ARRAY()重新组合为数组
无匹配元素时自动返回空数组{}

方法二：LATERAL子查询优化（适合大数据量）

如果数组元素较多，推荐用LATERAL子查询只拆分一次数组，再通过聚合函数的FILTER子句分别过滤：

SELECT
    t.id,
    t.bar,
    COALESCE(fo.young_foos, '{}'::text[]) AS young_foos,
    COALESCE(fo.old_foos, '{}'::text[]) AS old_foos
FROM your_table t
LEFT JOIN LATERAL (
    SELECT
        ARRAY_AGG(elem) FILTER (WHERE elem LIKE '%Young%') AS young_foos,
        ARRAY_AGG(elem) FILTER (WHERE elem LIKE '%Old%') AS old_foos
    FROM unnest(t.foos) elem
) AS fo ON true;