how to convert a column to an array in presto

Postgres provides a way to turn a column into an array. Samle query:

WITH
    dataset AS (
        SELECT '1' as a
        union
        SELECT '2' as a
        union
        SELECT '3' as a
    )
SELECT
    array(select a from dataset) as array_result;

returns

| array_result |
|--------------|
| {2,3,1}      |

How to do the same with Presto SQL?

Answer

Use array_agg(x [ORDER BY y]) aggregation function:

WITH
    dataset AS (
        SELECT '1' as a
        union all
        SELECT '2' as a
        union all
        SELECT '3' as a
    )
SELECT
    array_agg(a) as array_result from dataset;

Result:

['3', '2', '1']

If you need array<int>, cast before aggregation: array_agg(cast(a as int))