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?
To add values within an array, use SUM , as in the following example. To aggregate multiple rows within an array, use array_agg . For information, see Creating arrays from subqueries.
The purpose of unnest function in PostgreSQL is to expand the array into rows. Unnest function generates a table structure of an array in PostgreSQL. Unnest array function is beneficial in PostgreSQL for expanding the array into the set of values or converting the array into the structure of the rows.
We access array elements using the subscript within square brackets [] . By default, PostgreSQL uses one-based numbering for array elements. It means the first array element starts with number 1.
To define a new aggregate function, one selects a data type for the state value, an initial value for the state, and a state transition function. The state transition function takes the previous state value and the aggregate's input value(s) for the current row, and returns a new state value.
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
You can unnest and then group by:
WITH x (id, arr) AS (
VALUES
(1, ARRAY[1,2,3,4])
, (1, ARRAY[5,6])
, (1, ARRAY[7, 8])
)
SELECT id, ARRAY_AGG(splitup)
FROM (
SELECT
id
, unnest(arr) splitup
FROM x) splitup
GROUP BY 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With