group by/ aggregate in sql presto athena

Question

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.

Answer 1

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