# 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.

Source: https://docs.querylab.io/bigquery/arrays/

Updated: 2026-10-06

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](https://docs.querylab.io/bigquery/unnest/).

## Building arrays

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

**literals and generated arrays**

```sql
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
```

Result in BigQuery (1 row):

| numbers | empty_strings | steps | months |
| --- | --- | --- | --- |
| [1, 2, 3] | [] | [0, 5, 10, 15, 20] | [2026-01-01, 2026-02-01, 2026-03-01] |

`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:

**four ways to index**

```sql
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
```

Result in BigQuery (1 row):

| first_zero_based | offset_0 | ordinal_1 | safe_out_of_range | length |
| --- | --- | --- | --- | --- |
| a | a | a | NULL | 3 |

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

**OFFSET(5) on a three-element array**

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

BigQuery rejects this query:

```text
Array index 5 is out of bounds (overflow)
```

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":

**each user's page path and last page**

```sql
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
```

Result in BigQuery (2 rows):

| user_id | path | last_page |
| --- | --- | --- |
| u1 | [home, pricing, signup] | signup |
| u2 | [docs, home] | home |

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](https://docs.querylab.io/bigquery/null-handling/).

## 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:

**the common array functions**

```sql
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
```

Result in BigQuery (1 row):

| concatenated | reversed | joined | split_string | mapped_filtered |
| --- | --- | --- | --- | --- |
| [1, 2, 3] | [3, 2, 1] | bq-sql | [a, b, c] | [20, 30] |

The same pattern removes duplicates and sorts:

**distinct sorted elements**

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

Result in BigQuery (1 row):

| unique_sorted |
| --- |
| [1, 2, 3] |

## What arrays can't do

### Contain another array

BigQuery has no arrays of arrays:

**[[1, 2], [3]]**

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

BigQuery rejects this query:

```text
Cannot construct array with element type ARRAY<INT64> because nested arrays are not supported at [1:9]
```

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

**an array of structs holding arrays**

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

Result in BigQuery (1 row):

| matrix |
| --- |
| [{row_values: [1, 2]}, {row_values: [3]}] |

### Contain NULL

An array in a query result or a table can't hold a NULL element. See [Arrays can't hold NULL](https://docs.querylab.io/bigquery/null-handling/#arrays-cant-hold-null).

### Be compared with =

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

**comparing two arrays**

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

BigQuery rejects this query:

```text
Equality is not defined for arguments of type ARRAY<INT64> at [1:8]
```

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

**compare the JSON or joined text**

```sql
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
```

Result in BigQuery (1 row):

| same_json | same_text |
| --- | --- |
| true | true |

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

**grouping by an array**

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

Result in BigQuery (1 row):

| a | n |
| --- | --- |
| [1, 2] | 1 |

## 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](https://docs.querylab.io/bigquery/nested-and-repeated-data/) and the cost section of [UNNEST](https://docs.querylab.io/bigquery/unnest/#what-it-costs).

Read an array with OFFSET and ORDINAL. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Work with arrays](https://cloud.google.com/bigquery/docs/arrays)

- [Array functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions)

- [Data types, array type](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#array_type)