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.


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

  • Could you explain a bit further? Why is a CROSS JOIN necessary? What's that x(...) alias syntax? – OrangeDog Jul 3 '19 at 14:33
  • 1
    @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 – a_horse_with_no_name Jul 3 '19 at 14:34
  • I meant as opposed to some other join. The lateral stops it actually computing a full product? – OrangeDog Jul 3 '19 at 14:39
  • Using simply as v also works. – OrangeDog Jul 3 '19 at 14:39
  • @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. – a_horse_with_no_name Jul 3 '19 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.