CODEWITHSUNDEEP
arrayspostgresql
mrtyormaa
mrtyormaa

Reputation: 902

How to Group by and concatenate arrays in PostgreSQL

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.

Upvotes: 18

Views: 17715

Answers (2)

Ryabchenko Alexander
Ryabchenko Alexander

Reputation: 12380

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

Upvotes: 3

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

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.

Upvotes: 21

Related Questions