How to Group by and concatenate arrays in PostgreSQL

Question

I have a table in PostgreSQL. I want to concatenate all the arrays(i.e. col) after grouping them by time. The arrays are of varying dimensions.

| time  | col               |
|------ |------------------ |
| 1     | {1,2}             |
| 1     | {3,4,5,6}         |
| 2     | {}                |
| 2     | {7}               |
| 2     | {8,9,10}          |
| 3     | {11,12,13,14,15}  |

The result should be as follows:

| time  | col               |
|------ |------------------ |
| 1     | {1,2,3,4,5,6}     |
| 2     | {7,8,9,10}        |
| 3     | {11,12,13,14,15}  |

What I have come up with so far is as follows:

SELECT ARRAY(SELECT elem FROM tab, unnest(col) elem);

But this does not do the grouping. It just takes the entire table and concatenates it.

Kamil Gosciminski · Accepted Answer · 2018-06-29 12:54:55Z

22

To preserve the same dimension of you array you can't directly use array_agg(), so first we unnest your arrays and apply distinct to remove duplicates (1). In outer query this is the time to aggregate. To preserve values ordering include order by within aggregate function:

select time, array_agg(col order by col) as col
from (
  select distinct time, unnest(col) as col
  from yourtable
) t
group by time
order by time

(1) If you don't need duplicate removal just remove distinct word.

edited Jun 29, 2018 at 12:54

answered Jun 29, 2018 at 12:46

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

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

1 Comment

mrtyormaa Over a year ago

This is removing duplicates from the arrays too.

Ryabchenko Alexander · Accepted Answer · 2022-05-31 15:49:41Z

3

you can use next query

 SELECT 
      array_agg(_unnested.item) as array_coll
 from my_table
 left join LATERAL (SELECT unnest(my_table.array_coll) as item) _unnested ON TRUE

answered May 31, 2022 at 15:49

Ryabchenko Alexander

12.7k9 gold badges74 silver badges105 bronze badges

Collectives™ on Stack Overflow

How to Group by and concatenate arrays in PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related