Menu
Amazon Athena
User Guide

Creating Arrays

To build an array literal in Athena, use the ARRAY keyword, followed by brackets [ ], and include the array elements separated by commas.

Examples

This query creates one array with four elements.

SELECT ARRAY [1,2,3,4] AS items

It returns:

+-----------+ | items | +-----------+ | [1,2,3,4] | +-----------+

This query creates two arrays.

SELECT ARRAY[ ARRAY[1,2], ARRAY[3,4] ] AS items

It returns:

+--------------------+ | items | +--------------------+ | [[1, 2], [3, 4]] | +--------------------+

To create an array from selected columns of compatible types, use a query, as in this example:

WITH dataset AS ( SELECT 1 AS x, 2 AS y, 3 AS z ) SELECT ARRAY [x,y,z] AS items FROM dataset

This query returns:

+-----------+ | items | +-----------+ | [1,2,3] | +-----------+

In the following example, two arrays are selected and returned as a welcome message.

WITH dataset AS ( SELECT ARRAY ['hello', 'amazon', 'athena'] AS words, ARRAY ['hi', 'alexa'] AS alexa ) SELECT ARRAY[words, alexa] AS welcome_msg FROM dataset

This query returns:

+----------------------------------------+ | welcome_msg | +----------------------------------------+ | [[hello, amazon, athena], [hi, alexa]] | +----------------------------------------+

To create an array of key-value pairs, use the MAP operator that takes an array of keys followed by an array of values, as in this example:

SELECT ARRAY[ MAP(ARRAY['first', 'last', 'age'],ARRAY['Bob', 'Smith', '40']), MAP(ARRAY['first', 'last', 'age'],ARRAY['Jane', 'Doe', '30']), MAP(ARRAY['first', 'last', 'age'],ARRAY['Billy', 'Smith', '8']) ] AS people

This query returns:

+-----------------------------------------------------------------------------------------------------+ | people | +-----------------------------------------------------------------------------------------------------+ | [{last=Smith, first=Bob, age=40}, {last=Doe, first=Jane, age=30}, {last=Smith, first=Billy, age=8}] | +-----------------------------------------------------------------------------------------------------+

On this page: