CODEWITHSUNDEEP
sqlarrayspostgresqlaggregatesql-null
Esteban Zimanyi
Esteban Zimanyi

Reputation: 201

PostgreSQL and array_agg: Removing null values resulting in multiple arrays

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 ?

Upvotes: 0

Views: 932

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 1

richyen
richyen

Reputation: 9958

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)

Upvotes: 0

Related Questions