Amazon Athena
User Guide

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