如何在PostgreSQL中使用array_agg排除空值，就像在string_agg中一样？

Question

如果我使用array_agg来收集名称，那么我得到的名字将用逗号分隔，但是如果存在null值，那么该null也会被视为聚合中的一个名称。例如：

SELECT g.id,
       array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END) canonical_users,
       array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END) non_canonical_users
FROM groups g
GROUP BY g.id;

它返回的是,Larry,Phil，而不仅仅是Larry,Phil（在我的9.1.2中，它显示为NULL,Larry,Phil）。

相反，如果我使用string_agg()，它只显示名称（不带空逗号或null）。

问题在于服务器上安装了Postgres 8.4，而string_agg()在那里无法使用。是否有办法使array_agg的工作方式类似于string_agg()？

- Daud

---

很抱歉，我认为那个帖子中没有解决方案。 - Daud

那个线程中有两个解决方案。一个是创建一个函数，另一个（只是建议而没有展示）就是我回答的那个。 - Clodoaldo Neto

@Clodoaldo - 所有行都将具有canonical in ('y','n')...因此where子句似乎是多余的。问题在于，在分组内，如果canonical字段的值为'Y'，而我们正在收集'N'，那么null也会被收集。 - Daud

好的。现在我明白了。请检查更新后的答案。 - Clodoaldo Neto

感谢您的一切。array_to_string 对我有用。 - Daud

Answer 1

在postgresql-9.3中，可以这样做：

SELECT g.id,
   array_remove(array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END), NULL) canonical_users,
   array_remove(array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END), NULL) non_canonical_users
FROM groups g 
GROUP BY g.id;

更新: 适用于postgresql-9.4;

SELECT g.id,
   array_agg(g.users) FILTER (WHERE g.canonical = 'Y') canonical_users,
   array_agg(g.users) FILTER (WHERE g.canonical = 'N') non_canonical_users
FROM groups g 
GROUP BY g.id;

更新（2022-02-19）：同时适用于postgresql-9.4。

当数组中所有值都为null时，将结果转换为空数组而不是返回null。

SELECT g.id,
  coalesce( array_agg(g.users) FILTER (WHERE g.canonical = 'Y'), '{}' ) canonical_users,
  coalesce( array_agg(g.users) FILTER (WHERE g.canonical = 'N'), '{}' ) non_canonical_users
FROM groups g 
GROUP BY g.id;

- Dale O'Brien

---

这个方法快速而优雅地解决了我的问题，类似于提问者的问题。这是一个升级到9.3版本的理由，对于那些还没有升级的人来说。+1 - Pavel V.

9.4 更加优雅，运行得像魔法一样顺畅。 - jmgarnier

9.4变种甚至更好，因为在我这种情况下需要过滤掉的是null值。 - coladict

如果显而易见的话，对于其他情况，您可以执行类似于array_agg(col_to_aggregate) FILTER (WHERE col_to_aggregate IS NOT NULL)这样的操作，如果您只想直接将过滤器应用于该列。 - Stephen

@ansavchenco，我已经添加了一个更新，使其可以返回一个空数组而不是null。 - Dale O'Brien

Answer 2

如果您正在寻找一个现代化的答案来回答如何从数组中删除NULL元素的一般问题，那么答案是：

array_remove(your_array, NULL)

我特别关注性能，并希望将其与最佳替代方案进行比较：

CREATE OR REPLACE FUNCTION strip_nulls(
    IN array_in ANYARRAY
)
RETURNS anyarray AS
'
SELECT
    array_agg(a)
FROM unnest(array_in) a
WHERE
    a IS NOT NULL
;
'
LANGUAGE sql
;

进行pgbench测试证明（高置信度）array_remove()的速度是原先的两倍多一点。我在双精度数字上进行了测试，数组大小有所变化（10、100和1000个元素），其中随机插入了NULL值。

---

值得注意的是，这可以用于删除空白（''！= NULL）。但第二个参数接受anyelement类型，因为您很可能会用字符串文字表示空白，所以请确保将其转换为所需形式，通常是非数组形式。

例如：

select array_remove(array['abc', ''], ''::text);

如果您尝试：

select array_remove(array['abc', ''], '');

它会认为''是TEXT[]（数组）并引发此错误：

ERROR: malformed array literal: ""

- Alexi Theodore

---

@VivekSinha 你使用的是哪个版本的Postgres？我刚刚测试了你的查询，结果对我来说是"{1,2,3}"。我正在使用12.1版本。 - Alexi Theodore

啊，我明白了 @alexi-theodore，在我的端口发生了什么。我使用了一个自定义+修改的postgres驱动程序。当我直接在控制台查询时，我可以看到正确的输出！对于混淆感到抱歉。删除之前的评论并点赞答案！ - Vivek Sinha

可能需要注意的是，自9.3版本以来支持array_remove函数。 - Anatoly Rugalev

Answer 3

select
    id,
    (select array_agg(a) from unnest(canonical_users) a where a is not null) canonical_users,
    (select array_agg(a) from unnest(non_canonical_users) a where a is not null) non_canonical_users
from (
    SELECT g.id,
           array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END) canonical_users,
           array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END) non_canonical_users
    FROM groups g
    GROUP BY g.id
) s

或者，更简单、可能更便宜的方法是使用 array_to_string 函数来消除空值：

SELECT
    g.id,
    array_to_string(
        array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END)
        , ','
    ) canonical_users,
    array_to_string(
        array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END)
        , ','
    ) non_canonical_users
FROM groups g
GROUP BY g.id

- Clodoaldo Neto

---

谢谢。但是，如果主查询返回1000行，则使用unnest的2个子查询将为每一行运行一次。容忍NULL是否比执行2000个额外的select查询更好？ - Daud

@Daud 新版本可能更便宜。请确保获取两个版本的解释输出。 - Clodoaldo Neto

如果您正在使用“array_to_string(array_agg(...))”，那么最好使用“string_agg”。 - Craig Ringer

@Craig，问题在于题目中的8.4。 - Clodoaldo Neto

@Clodoaldo Gah，老版本。谢谢。 - Craig Ringer

Answer 4

解决从数组聚合中删除空值的一般问题有两种主要方法：一种是执行array_agg(unnest(array_agg(x)))，另一种是创建自定义聚合函数。

第一种方法的形式如上所示（链接）:

SELECT 
    array_agg(u) 
FROM (
    SELECT 
        unnest(
            array_agg(v)
        ) as u 
    FROM 
        x
    ) un
WHERE 
    u IS NOT NULL;

第二个：

/*
With reference to
http://ejrh.wordpress.com/2011/09/27/denormalisation-aggregate-function-for-postgresql/
*/
CREATE OR REPLACE FUNCTION fn_array_agg_notnull (
    a anyarray
    , b anyelement
) RETURNS ANYARRAY
AS $$
BEGIN

    IF b IS NOT NULL THEN
        a := array_append(a, b);
    END IF;

    RETURN a;

END;
$$ IMMUTABLE LANGUAGE 'plpgsql';

CREATE AGGREGATE array_agg_notnull(ANYELEMENT) (
    SFUNC = fn_array_agg_notnull,
    STYPE = ANYARRAY,
    INITCOND = '{}'
);

调用第二个查询语句看起来比第一个更加优雅：

select array_agg_notnull(v) from x;

- rorycl

Answer 5

虽然这个帖子相当古老，但我还是想分享一个很好的技巧，在处理小型数组时可以非常有效。它在Postgres 8.4+上运行而无需额外的库或函数。

string_to_array(array_to_string(array_agg(my_column)))::int[]

array_to_string() 方法实际上会去掉 null 值。

- ced-b

Answer 6

你应该使用array_remove将array_agg进行包装。

SELECT g.id,
       array_remove(array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END), NULL) canonical_users,
       array_remove(array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END), NULL) non_canonical_users
FROM groups g
GROUP BY g.id;

- Lulu

Answer 7

如评论中所建议的，您可以编写一个函数来替换数组中的null值。然而，正如在评论中链接到的线程中指出的那样，如果您必须创建一个聚合，然后再拆分它并重新进行聚合，这种方法会降低聚合函数的效率。

我认为在数组中保留null值只是Array_Agg的一个（或许不受欢迎的）特性。您可以使用子查询来避免这种情况：

SELECT  COALESCE(y.ID, n.ID) ID,
        y.Users,
        n.Users
FROM    (   SELECT  g.ID, ARRAY_AGG(g.Users) AS Users
            FROM    Groups g
            WHERE   g.Canonical = 'Y'
            GROUP BY g.ID
        ) y
        FULL JOIN 
        (   SELECT  g.ID, ARRAY_AGG(g.Users) AS Users
            FROM    Groups g
            WHERE   g.Canonical = 'N'
            GROUP BY g.ID
        ) n
            ON n.ID = y.ID

SQL FIDDLE

- GarethD

---

谢谢。但我需要使用“case”来处理给定分组内的行，而子查询在那里会很低效。 - Daud

Answer 8

我做了一个array_except，概念是array_except(x,y)。

array_except(array_agg(x), array_agg(case when x is null then x end))

- Casey

Answer 9

这很简单，首先为 text[] 创建一个新的 -（减号） 运算符：

CREATE OR REPLACE FUNCTION diff_elements_text
    (
        text[], text[] 
    )
RETURNS text[] as 
$$
    SELECT array_agg(DISTINCT new_arr.elem)
    FROM
        unnest($1) as new_arr(elem)
        LEFT OUTER JOIN
        unnest($2) as old_arr(elem)
        ON new_arr.elem = old_arr.elem
    WHERE old_arr.elem IS NULL
$$ LANGUAGE SQL IMMUTABLE;

CREATE OPERATOR - (
    PROCEDURE = diff_elements_text,
    leftarg = text[],
    rightarg = text[]
);

只需简单地减去数组[null]：

select 
    array_agg(x)-array['']
from
    (   select 'Y' x union all
        select null union all
        select 'N' union all
        select '' 
    ) x;

就是这样：

{是，否}

- Miklos

---

array_agg(x) FILTER (WHERE x is not null) 看起来更简单： https://dbfiddle.uk/?rdbms=postgres_12&fiddle=92da5321389fa18265aa415072377b98，你不需要自己编写函数，可以直接使用 array_remove() https://dbfiddle.uk/?rdbms=postgres_12&fiddle=4149f45f6304623720ac51a4cefb918a。 - user330315