sqlarrayspostgresqlaggregate-functions

postgresql Aggregate arrays into a single array with union of all elements

I'm looking for a sql pattern for an aggregate function to aggregate arrays. If I have 2 rows:

|id  |     array     |
|----+---------------|
|1   |    [1,2,3,4]  |
|1   |    [5,6]      |
|1   |    [7,8]      |
|--------------------|

And I want to do the following:

select id, *aggregate_function*(array) from table group by id

I want the result to be:

|id   | *aggregate_function*  |
|-----+-----------------------|
|1    | [1,2,3,4,5,6,7,8]     |
|-----------------------------|

There is no native postgres function that does this aggregation. But perhaps there's sql pattern that can be used here?

Solution

  • Something like this should work:

    with mytable as
(
select 1 as id, array[1, 2, 3, 4] as myarray

union all

select 1 as id, array[5, 6] as myarray

union all 

select 1 as id, array[7, 8] as myarray
)

select
  id,
  array_agg(elements order by elements)
from mytable, unnest(myarray) as elements
group by id

    There's some discussion about building the custom function here: Concatenate/merge array values during grouping/aggregation