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

• user1822: @OrangeDog: you need the cross join (lateral actually) because otherwise you can't unnest to a sub-query (derived table). The x() is just a regular table alias that includes a column alias
• user1822: @OrangeDog: as v does not provide a column alias it just happens to work because the table and column alias are the same then. Using an explicit column alias is cleaner code.
• OrangeDog: It appears that LATERAL isn't necessary - I get the same results and query plan both with and without it.
• user1822: @OrangeDog: yes, it's optional when the (lateral) join is against a set returning function. I prefer to write it nevertheless as explained in the manual
• OrangeDog: It appears that other aggregates are going to give unexpected results with this join.

Answer 2

This also works:

SELECT array_agg(DISTINCT all_values) as uniq_values
FROM 
(SELECT key, 
 unnest(values) as all_values
FROM data) q1
GROUP BY key