Postgres has an aggregation function for combining values into lists, but it seems to lack one for concatenating arrays in a GROUP BY.
Fortunately, this is easy to fix:
CREATE AGGREGATE flatMap (anyarray)
(
sfunc = array_cat,
stype = ANYARRAY,
initcond = '{}'
);
Example:
with testData as (
SELECT
'group1' val,
ARRAY [1, 2] my_data
UNION ALL
SELECT
'group1' val,
ARRAY [1, 3] my_data
UNION ALL
SELECT
'group2' val,
ARRAY [4] my_data
)
select flatMap(my_data), val
from testData
group by val
Here are the results:
"{1,2,1,3}",group1 {4},group2