Combining array_agg and unnest

Question

Given a dataset (with a GIN index on values):

key | values
-------------
 1  | {4,2,1}
 1  | {2,5}
 2  | {4,1,3}

I want to aggregate the arrays:

key | values
-------------
 1  | {4,2,1,5}
 2  | {4,1,3}

My first thought didn't work:

SELECT key, array_agg(DISTINCT unnest(values)) AS values FROM data GROUP BY key

[0A000] ERROR: aggregate function calls cannot contain set-returning function calls
Hint: You might be able to move the set-returning function into a LATERAL FROM item.

Not being familiar with LATERAL FROM, it's not obvious to me how to achieve the desired output.

Answer 1

You need to do the unnest in a sub-query:

select d."key", array_agg(distinct x.v) 
from data d
  cross join lateral unnest(d."values") as x(v)
group by d."key";

Set returning functions (like unnest()) should in general be used in the from part of a query. But to be able to reference a column from the table you need a lateral join.

from data cross join lateral unnest(...) is an explicit way of writing from data, unnest(...) which also generates a cross join. But I prefer the explicit cross join operator to document that I indeed intended to write a cross join, rather than accidentally.

This will however not preserve the order of the elements.

Online example: https://rextester.com/TVIDB57711