12

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.

2 Answers 2

23

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

5
  • 4
    @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
    Jul 3, 2019 at 14:34
  • @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.
    – user1822
    Jul 3, 2019 at 14:42
  • It appears that LATERAL isn't necessary - I get the same results and query plan both with and without it.
    – OrangeDog
    Jul 4, 2019 at 14:27
  • @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
    – user1822
    Jul 4, 2019 at 14:28
  • It appears that other aggregates are going to give unexpected results with this join.
    – OrangeDog
    Jul 4, 2019 at 14:45
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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