Skip to content

Working with Arrays

An Array is an ordered collection of zero or more VARCHAR values.

Actions

Casting

Cast values to array type:

CAST(column AS ARRAY<element_type>)

Example:

SELECT CAST('[1, 2, 3]' AS ARRAY<INTEGER>);

Create

Array Literal

Create an array using literal notation:

[<value>, <value>, ...]

Example:

SELECT ['Mercury', 'Gemini', 'Apollo'] AS missions;

Split String

Create an array by splitting a string:

SPLIT(string)

Example:

SELECT SPLIT('apple,banana,cherry');

Reading

Subscript Access

Access individual elements by index:

array[index]

Example:

SELECT missions[0]
  FROM $astronauts;

Get Length

Get the number of elements in an array:

LENGTH(array)

Example:

SELECT LENGTH(missions)
  FROM $astronauts;

Get Minimum/Maximum

Get the smallest or largest value in an array:

LEAST(array)
GREATEST(array)

Example:

SELECT LEAST([5, 2, 8, 1]) AS min_value,
       GREATEST([5, 2, 8, 1]) AS max_value;

Comparing

Equality

Compare arrays for equality:

SELECT *
  FROM table1
 WHERE array_column = ['value1', 'value2'];

ANY (Comparison)

value <operator> ANY (array)

The ANY function is used to apply a filter to each item in an array, and returns True if any item in the array matches the condition.

Example:

SELECT * 
  FROM $astronauts
 WHERE 'Apollo 11' = ANY (missions);

Supported operators: =, !=, >, <, >=, <=

ANY (Similarity)

column <operator> ANY (patterns)

The ANY modifier is used to perform a similarity search against all of the items in an array, and returns True if any item in the array matches the pattern.

Example:

SELECT *
  FROM $astronauts
 WHERE missions LIKE ANY ('Mercury%', 'Gemini%', 'Apollo%');

Supported operators: LIKE, NOT LIKE, ILIKE, NOT ILIKE

ALL

value <operator> ALL(array)

The ALL function is used to apply a filter to each item in an array, and returns True if all items in the array match the condition.

Example:

SELECT *
  FROM $astronauts
 WHERE 100 > ALL (mission_durations);

Note

ALL currently supports a subset of operators: =, !=

Containment Testing

ARRAY_CONTAINS

Test if an array contains a specific value:

ARRAY_CONTAINS(array, value)

Example:

SELECT *
  FROM $astronauts
 WHERE ARRAY_CONTAINS(missions, 'Apollo 11');
ARRAY_CONTAINS_ANY

Test if an array contains any of the specified values:

ARRAY_CONTAINS_ANY(array, values)

Or using the @> operator:

array @> values

The @> operator also supports JSON Path expressions for more complex queries on nested structures.

Example:

SELECT *
  FROM $astronauts
 WHERE ARRAY_CONTAINS_ANY(missions, ['Apollo 11', 'Apollo 13']);
ARRAY_CONTAINS_ALL

Test if an array contains all of the specified values:

ARRAY_CONTAINS_ALL(array, values)

Or using the @>> operator:

values @>> array

The @>> operator also supports JSON Path expressions for more complex queries on nested structures.

Example:

SELECT *
  FROM $astronauts
 WHERE ARRAY_CONTAINS_ALL(missions, ['Apollo 11', 'Apollo 13']);
IN Operator

Test if a value is in a list of values:

value IN (value1, value2, ...)

Example:

SELECT *
  FROM $planets
 WHERE name IN ('Earth', 'Mars');
IN UNNEST

Test if a value exists in an array column:

value IN UNNEST(array)

Example:

SELECT *
  FROM $astronauts
 WHERE 'Apollo 11' IN UNNEST(missions);

Transforms

Sort Array

Sort an array in ascending order:

SORT(array)

Example:

SELECT SORT([3, 1, 4, 1, 5]) AS sorted_array;

Converting Lists to Relations

Using UNNEST

UNNEST allows you to create a single column relation either as a list of literals, or from a column of LIST type in a dataset.

SELECT * 
  FROM UNNEST((True, False)) AS Booleans;

Limitations

Lists have the following limitations

  • Statements cannot ORDER BY a list column
  • Lists cannot be used in comparisons

Note

Some restrictions may be resolved by the query optimizer, for example, Projection Pushdown may remove list columns as part of optimization. However, you should not rely on the optimizer to take any particular action.