Arrays in BigQuery

How to build, index, aggregate and transform ARRAY values in BigQuery. Arrays can't contain arrays or NULLs, can't be compared with =, and are zero-based with OFFSET or one-based with ORDINAL.

An ARRAY is an ordered list of values of one type, stored in a single column. BigQuery uses arrays where other databases use a child table: the items of an order, the tags of a post, the events of a session. This page covers building and reading arrays. To turn an array into rows, see UNNEST.

Building arrays

Write an array literal with square brackets, generate one from a range, or give an empty array its element type explicitly:

SELECT
  [1, 2, 3] AS numbers,
  ARRAY<STRING>[] AS empty_strings,
  GENERATE_ARRAY(0, 20, 5) AS steps,
  GENERATE_DATE_ARRAY(DATE '2026-01-01', DATE '2026-03-01', INTERVAL 1 MONTH) AS months

GENERATE_ARRAY, GENERATE_DATE_ARRAY and GENERATE_TIMESTAMP_ARRAY are the usual way to build a calendar or a number series. Unnest the result to get one row per value.

Reading one element

Indexes are zero-based. [OFFSET(n)] is the explicit zero-based form, [ORDINAL(n)] the one-based form, and a bare [n] means OFFSET. The SAFE_ variants return NULL instead of failing when the index is past the end:

WITH t AS (SELECT ['a', 'b', 'c'] AS letters)
SELECT
  letters[0] AS first_zero_based,
  letters[OFFSET(0)] AS offset_0,
  letters[ORDINAL(1)] AS ordinal_1,
  letters[SAFE_OFFSET(5)] AS safe_out_of_range,
  ARRAY_LENGTH(letters) AS length
FROM t

Without SAFE_, an index past the end stops the whole query:

WITH t AS (SELECT ['a', 'b', 'c'] AS letters)
SELECT letters[OFFSET(5)] AS sixth
FROM t

Use SAFE_OFFSET whenever the arrays can have different lengths, which in real data is almost always.

Collecting rows into an array

ARRAY_AGG is the aggregate that builds an array from the rows of each group. It takes an ORDER BY for the element order and a LIMIT, so it also answers “the last value per group”:

WITH visits AS (
  SELECT 'u1' AS user_id, 'home' AS page, 1 AS seq UNION ALL
  SELECT 'u1', 'pricing', 2 UNION ALL
  SELECT 'u1', 'signup', 3 UNION ALL
  SELECT 'u2', 'docs', 1 UNION ALL
  SELECT 'u2', 'home', 2
)
SELECT
  user_id,
  ARRAY_AGG(page ORDER BY seq) AS path,
  ARRAY_AGG(page ORDER BY seq DESC LIMIT 1)[OFFSET(0)] AS last_page
FROM visits
GROUP BY user_id
ORDER BY user_id

Without ORDER BY inside ARRAY_AGG, the element order is not defined. If a value is NULL, the array ARRAY_AGG builds holds a NULL, and the query fails once that array reaches the result. Add IGNORE NULLS to drop them. See NULL in BigQuery.

Transforming an array

BigQuery has no map or filter function for arrays. Instead you unnest, transform with ordinary SQL, and collect again with the ARRAY(subquery) constructor:

SELECT
  ARRAY_CONCAT([1, 2], [3]) AS concatenated,
  ARRAY_REVERSE([1, 2, 3]) AS reversed,
  ARRAY_TO_STRING(['bq', 'sql'], '-') AS joined,
  SPLIT('a,b,c', ',') AS split_string,
  ARRAY(SELECT x * 10 FROM UNNEST([1, 2, 3]) AS x WHERE x > 1) AS mapped_filtered

The same pattern removes duplicates and sorts:

SELECT ARRAY(SELECT DISTINCT x FROM UNNEST([3, 1, 3, 2, 1]) AS x ORDER BY x) AS unique_sorted

What arrays can’t do

Contain another array

BigQuery has no arrays of arrays:

SELECT [[1, 2], [3]] AS matrix

Wrap the inner array in a struct. An array of structs that each hold an array is allowed:

SELECT [STRUCT([1, 2] AS row_values), STRUCT([3])] AS matrix

Contain NULL

An array in a query result or a table can’t hold a NULL element. See Arrays can’t hold NULL.

Be compared with =

Equality isn’t defined for arrays, so =, != and IN on array values fail:

SELECT [1, 2] = [1, 2] AS same

Compare a text form instead. TO_JSON_STRING works for any element type and keeps the order:

SELECT
  TO_JSON_STRING([1, 2]) = TO_JSON_STRING([1, 2]) AS same_json,
  ARRAY_TO_STRING(['a', 'b'], ',') = ARRAY_TO_STRING(['a', 'b'], ',') AS same_text

GROUP BY and SELECT DISTINCT on an array column do work, even though = doesn’t:

WITH t AS (SELECT [1, 2] AS a)
SELECT a, COUNT(*) AS n
FROM t
GROUP BY a

Limits

• An array can’t contain another array, or a NULL in output.
• A row, arrays included, can’t be larger than about 100 MB. Very large ARRAY_AGG results hit this limit; aggregate with a LIMIT or filter first.

Storage and cost

An array column of structs is stored as one column per struct field, so reading one field of an array of structs reads only that field. See Nested and repeated data and the cost section of UNNEST.