Amazon Athena
User Guide  | API Reference

Filtering Arrays

Create an array from a collection of rows if they match the filter criteria.

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)
WHERE i > 3

This query returns:

+-------------+
| array_items |
+-------------+
| [4, 5]      |
+-------------+

Filter an array based on whether one of its elements contain a specific value, such as 2, as in this example:

WITH
dataset AS (
  SELECT ARRAY
  [
    ARRAY[1,2,3,4],
    ARRAY[5,6,7,8],
    ARRAY[9,0]
  ] AS items
)
SELECT i AS array_items FROM dataset
CROSS JOIN UNNEST(items) AS t(i)
WHERE contains(i, 2)

This query returns:

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