How to concatenate arrays grouped by another column in Presto?

Question

Is this possible in SQL (preferably Presto):

I want to reshape this table:

id, array
1, ['something']
1, ['something else']
2, ['something']

To this table:

id, array
1, ['something', 'something else']
2, ['something']

Answer 1

In Presto you can use array_agg. Assuming that on input, all your arrays are single-element, this would look like this:

select id, array_agg(array[0])
from ...
group by id;

If, however, your input arrays are not necessarily single-element, you can combine this with flatten, like this:

select id, flatten(array_agg(array))
from ...
group by id;

Answer 2

If you want an array that shows the distinct items in the aggregated array then this should work:

select id, array_distinct(flatten(array_agg(array))) as array
from ...
group by id

Answer 3

You can also do set_union if you only need distinct values

select id, set_union(array) as array
from ...
group by id