Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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}] |
+-----------------------------------------------------------------------------------------------------+