Creating arrays from subqueries - Amazon Athena

Creating arrays from subqueries

Create an array from a collection of rows.

WITH dataset AS ( SELECT ARRAY[1,2,3,4,5] AS items ) SELECT array_agg(i) AS array_items FROM dataset CROSS JOIN UNNEST(items) AS t(i)

This query returns:

+-----------------+ | array_items | +-----------------+ | [1, 2, 3, 4, 5] | +-----------------+

To create an array of unique values from a set of rows, use the distinct keyword.

WITH dataset AS ( SELECT ARRAY [1,2,2,3,3,4,5] AS items ) SELECT array_agg(distinct i) AS array_items FROM dataset CROSS JOIN UNNEST(items) AS t(i)

This query returns the following result. Note that ordering is not guaranteed.

+-----------------+ | array_items | +-----------------+ | [1, 2, 3, 4, 5] | +-----------------+

For more information about using the array_agg function, see Aggregate functions in the Trino documentation.