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.