BigQuery array functions: syntax and tested examples

ARRAY_LENGTH

SELECT
  ARRAY_LENGTH([1, 2, 3]),   -- 3
  ARRAY_LENGTH([]),          -- 0
  ARRAY_LENGTH(CAST(NULL AS ARRAY<INT64>)); -- NULL

An empty array has length 0. A NULL array has length NULL, so WHERE ARRAY_LENGTH(tags) = 0 does not find rows where the column is NULL. Use IFNULL(ARRAY_LENGTH(tags), 0) = 0 to catch both.

Array to string, and string to array

SELECT
  ARRAY_TO_STRING(['a', 'b', NULL, 'c'], ', '),
  -- a, b, c
  ARRAY_TO_STRING(['a', NULL], ', ', 'n/a'),
  -- a, n/a
  SPLIT('a,b,c', ',');
  -- [a, b, c]

ARRAY_TO_STRING skips NULL elements unless you give a third argument to print in their place. It works on arrays of strings; cast other types first. SPLIT is the reverse and is covered with its edge cases in string functions.

UNNEST: array to rows

UNNEST turns an array into a table with one row per element. Joined to its own row, it flattens the array:

SELECT id, tag
FROM (
  SELECT 1 AS id, ['a', 'b'] AS tags
  UNION ALL
  SELECT 2, []
), UNNEST(tags) AS tag;
-- 1, a
-- 1, b

Row 2 has disappeared. A comma or CROSS JOIN with UNNEST drops rows whose array is empty. If those rows should stay, use LEFT JOIN:

SELECT id, tag
FROM (
  SELECT 1 AS id, ['a', 'b'] AS tags
  UNION ALL
  SELECT 2, []
)
LEFT JOIN UNNEST(tags) AS tag;
-- 1, a
-- 1, b
-- 2, NULL

This is the most common way a query over arrays loses data without an error. It is also the pattern behind every GA4 export query, explained in how to UNNEST GA4 event parameters.

To keep the position of each element, add WITH OFFSET:

SELECT x, i
FROM UNNEST([10, 20, 30]) AS x WITH OFFSET AS i;

ARRAY_AGG: rows to array

SELECT ARRAY_AGG(DISTINCT x ORDER BY x)
FROM UNNEST([3, 1, 3, 2]) AS x;
-- [1, 2, 3]

ARRAY_AGG is the aggregate that builds an array, usually with GROUP BY. It accepts DISTINCT, ORDER BY, LIMIT and IGNORE NULLS. Without IGNORE NULLS, a NULL in the input leads to the error in the section on nulls below.

Transform, filter or replace elements

There is no ARRAY_TRANSFORM and no ARRAY_REPLACE to call. On 6 October 2026 both returned “Function not found” in BigQuery. The work is done by one pattern: a subquery over UNNEST, wrapped in ARRAY().

SELECT
  -- transform each element
  ARRAY(SELECT x * 10
        FROM UNNEST([1, 2, 3]) AS x),
  -- [10, 20, 30]

  -- filter
  ARRAY(SELECT x
        FROM UNNEST([1, 2, 3, 4]) AS x
        WHERE x > 2),
  -- [3, 4]

  -- replace one value
  ARRAY(SELECT IF(x = 'b', 'z', x)
        FROM UNNEST(['a', 'b', 'c']) AS x),
  -- [a, z, c]

  -- replace NULLs
  ARRAY(SELECT IFNULL(x, 0)
        FROM UNNEST([1, NULL, 3]) AS x),
  -- [1, 0, 3]

  -- sort
  ARRAY(SELECT x
        FROM UNNEST([3, 1, 2]) AS x
        ORDER BY x);
  -- [1, 2, 3]

People often search for a way to do this “without UNNEST”. There is none, and there is no need for one: inside ARRAY(SELECT ...), the UNNEST is local to that expression. It does not multiply the rows of the outer query, which is the thing people are trying to avoid.

To keep the original order when you filter, add WITH OFFSET and order by it.

Check whether an array contains a value

SELECT
  2 IN UNNEST([1, 2, 3]),
  -- true
  EXISTS (
    SELECT 1 FROM UNNEST(['a', 'b']) AS x
    WHERE x LIKE 'b%'
  );
  -- true

value IN UNNEST(array) for an exact match, EXISTS with a subquery for a condition.

Get one element

SELECT
  [1, 2, 3][OFFSET(0)],        -- 1
  [1, 2, 3][ORDINAL(1)],       -- 1
  [1, 2, 3][SAFE_OFFSET(5)],   -- NULL
  ARRAY_FIRST([1, 2, 3]),      -- 1
  ARRAY_LAST([1, 2, 3]),       -- 3
  ARRAY_SLICE([1, 2, 3, 4, 5], 1, 3);
  -- [2, 3, 4]

OFFSET counts from 0 and ORDINAL from 1. Both stop the query when the position does not exist. On real data use SAFE_OFFSET or SAFE_ORDINAL, which return NULL.

GENERATE_ARRAY and GENERATE_DATE_ARRAY

SELECT
  GENERATE_ARRAY(1, 10, 3),
  -- [1, 4, 7, 10]
  GENERATE_DATE_ARRAY(
    DATE '2026-10-01', DATE '2026-10-04'),
  -- [2026-10-01, 2026-10-02,
  --  2026-10-03, 2026-10-04]
  GENERATE_DATE_ARRAY(
    DATE '2026-01-01', DATE '2026-04-01',
    INTERVAL 1 MONTH);
  -- [2026-01-01, 2026-02-01,
  --  2026-03-01, 2026-04-01]

Both ends are included. The default step for dates is one day. The standard use is a date spine: one row per day, so that days with no data still appear in a report.

SELECT day
FROM UNNEST(GENERATE_DATE_ARRAY(
  DATE '2026-10-01', DATE '2026-10-31')) AS day;

Left-join your facts to that, and a day without orders shows as a row with zero instead of a gap. More date arithmetic in date functions.

ARRAY_CONCAT and ARRAY_REVERSE

SELECT
  ARRAY_CONCAT([1, 2], [3]),  -- [1, 2, 3]
  ARRAY_REVERSE([1, 2, 3]);   -- [3, 2, 1]

Aggregating inside an array

To sum or average the elements of an array without flattening the row, use a scalar subquery:

SELECT (SELECT SUM(x) FROM UNNEST([1, 2, 3]) AS x);
-- 6

The same shape works for MAX, AVG, COUNT and COUNTIF.

“Array cannot have a null element”

BigQuery can hold a NULL inside an array while it computes, but it cannot return or store one. Selecting [1, NULL, 3] as a result column fails with:

Array cannot have a null element;
error in writing field

Remove the nulls or replace them before the array leaves the query:

SELECT ARRAY(
  SELECT x FROM UNNEST([1, NULL, 3]) AS x
  WHERE x IS NOT NULL
);
-- [1, 3]

With ARRAY_AGG, add IGNORE NULLS.

Mistakes to avoid

• , UNNEST() on a table with empty arrays silently drops those rows. Use LEFT JOIN UNNEST.
• Unnesting two arrays in one query multiplies them: 3 tags and 4 items become 12 rows, and every sum is inflated.
• OFFSET past the end is an error. Use SAFE_OFFSET.
• ARRAY_LENGTH of NULL is NULL, not 0.
• Array order is only guaranteed when you set it with ORDER BY in ARRAY_AGG or in the ARRAY() subquery.

Flatten once, read many times

Arrays are compact to store and easy to get wrong when read. The lost rows from a cross join and the inflated sums from two unnests are both plausible-looking results, and each new query over the nested table is a new chance to produce one.

A common answer is to do the flattening in one query that everyone else reads as a plain table. In OWOX Data Marts that query is a Data Mart: your SQL, with the LEFT JOIN UNNEST already right, a description and an owner, run in your warehouse. OWOX does not flatten or reshape the data for you. It keeps the version you wrote as the one the reports use.

Google BigQueryBigQuery datetime functions: syntax and tested examplesOctober 6, 2026→Google BigQueryFull Guide to Using Wildcard Tables in BigQuery for 2025August 8, 2025→Google BigQueryEnhanced Data Analysis with BigQuery Navigation FunctionsMay 26, 2024→

See all articles →