Search code examples
sqlamazon-athenaprestotrino

group by/ aggregate in sql presto athena

I have a table which looks like so:

id, author
1, {entries..}
1, {entries2..}
1, {entries3..}
2, {entry x}
2, {entry y}
</snip>

I would like to group by and produce another table which looks like so:

id, author
1, [{entries..},{entries2..},{entries3..}]
2, [{entry x},{entry y}]
</snip>

ie I want to group by ID and store all the results in an array table in athena/presto.

Solution

  • You can do it using the aggregate function array_agg() to return an array created from a specific column :

    SELECT id,array_agg(author) as author
FROM yourTable
GROUP BY id

    The Insert can be :

    INSERT INTO anotherTable
SELECT id, array_agg(author) as author
FROM yourtable
GROUP BY id