“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


Need help with Postgres? Contact me for Postgres consulting.

Leave a Reply

Your email address will not be published. Required fields are marked *