Posted on

“flatMap” in Postgres

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