PostgreSQL and array_agg: Removing null values resulting in multiple arrays

Question

I have a table with two columns: k is the key, and a may contain null values. An example is as follows:

drop table if exists test;
create table test(k, a) as
select * from ( values
(1, 1),
(2, 2),
(3, 3),
(4, NULL),
(5, NULL),
(6, 6),
(7, 7),
(8, NULL),
(9, 9),
(10, 10)
) t;

I would need to aggregate the values of column a ordered by column k into several arrays without null values. Using array_agg and filter is NOT what I need

select array_agg(a order by k)  from test
-- "{1,2,3,NULL,NULL,6,7,NULL,9,10}"

select array_agg(a order by k) filter (where a is not null) from test
-- "{1,2,3,6,7,9,10}"

What I need to obtain is as follows

"{1,2,3}"
"{6,7}"
"{9,10}"

Any idea how to achieve this ?

Gordon Linoff · Accepted Answer · 2019-12-27 02:56:32Z

1

You can define the groups by counting the number of NULL values up-to-each row. The rest is then just filtering and aggregation:

select array_agg(k order by a)
from (select t.*,
             count(*) filter (where a is null) over (order by k) as grp
      from test t
     ) t
where a is not null
group by grp;

Here is a db<>fiddle.

answered Dec 27, 2019 at 2:56

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

richyen · Accepted Answer · 2019-12-27 02:22:38Z

0

Perhaps some combination of string_to_array and regexp_split_to_table may work:

postgres=# select string_to_array(trim(both ',' from arr),',') as sta from (select regexp_split_to_table(array_to_String(array_agg(a order by k),',','*'),'\*') as arr from test) as foo where foo.arr <> ',';
   sta   
---------
 {1,2,3}
 {6,7}
 {9,10}
(3 rows)

answered Dec 27, 2019 at 2:22

richyen

10.3k4 gold badges18 silver badges36 bronze badges

Collectives™ on Stack Overflow

PostgreSQL and array_agg: Removing null values resulting in multiple arrays

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related