Docs/Error messages/Array cannot have a null element
Error messagesData values

Array cannot have a null element; error in writing field X

An array with a NULL in it can live inside a query, but it cannot be returned, saved or read from a view. ARRAY_AGG without IGNORE NULLS is a common source. The fix is to drop the NULLs or replace them before the array leaves the query.

Practice this in a free lesson on learn.querylab.io: Collect values with ARRAY_AGG.

Array cannot have a null element; error in writing field a

What BigQuery means

An array can hold a NULL element while the query works on it. When the array becomes a column of the result, a table or a view’s output, BigQuery refuses it. The name at the end of the message is the output column.

BigQuery’s data types page says the same: it raises an error if the query result has an array that contains NULL elements, although such an array can be used inside the query.

The error is raised when the query runs. A dry run passes.

The smallest query that reproduces it

SELECT [1, NULL] AS a
Array cannot have a null element; error in writing field a

The fix depends on where the NULL comes from:

SELECT ARRAY_AGG(x IGNORE NULLS) AS a FROM UNNEST([1, NULL, 3]) AS x

This returns [1, 3].

What makes the array

All of these gave the message above, with the column name from the query.

QueryWhere the NULL comes from
SELECT [1, NULL] AS athe array literal
SELECT ARRAY_AGG(x) AS a FROM UNNEST([1, NULL, 3]) AS xa NULL in the rows
SELECT ARRAY_AGG(x ORDER BY x) ..., ARRAY_AGG(DISTINCT x) ..., ARRAY_AGG(x LIMIT 5) ...the same, with the other clauses
SELECT ARRAY_AGG(SAFE_CAST(x AS INT64)) FROM UNNEST(['1', 'z']) AS xSAFE_CAST made a NULL
SELECT ARRAY_AGG(NULLIF(x, '')) ...NULLIF made a NULL
SELECT u.id, ARRAY_AGG(o.amt) FROM u LEFT JOIN o ON ... GROUP BY u.ida row of u with no match in o
SELECT ARRAY(SELECT x FROM UNNEST([1, NULL, 3]) AS x) AS athe subquery returns a NULL row
SELECT ARRAY_CONCAT([1], [NULL]) AS aa NULL in an input array
SELECT ARRAY_REVERSE([1, NULL]) AS athe same
SELECT JSON_VALUE_ARRAY(JSON '{"k":["x",null]}', '$.k') AS ba JSON null in the array
SELECT ARRAY_AGG(x) OVER () AS a FROM UNNEST([1, NULL, 3]) AS xthe aggregate used as a window function
SELECT [1, 2, NULL] AS a UNION ALL SELECT [3]one branch of a set operation

The message variants

A nested array. A STRUCT field, or a struct in an array, adds one clause for each level:

SELECT STRUCT([1, NULL] AS a) AS s
Array cannot have a null element; error in writing field s.a; error in writing field s

SELECT [STRUCT([1, NULL] AS a)] AS s gives the same text.

Where the query ends up. The text is the same wherever the array is written. These all gave it:

  • a plain SELECT that returns the array
  • SELECT * FROM (SELECT [1, NULL] AS a)
  • WITH t AS (SELECT [1, NULL] AS a) SELECT a FROM t
  • CREATE TABLE ... AS SELECT [1, NULL] AS a
  • INSERT INTO t SELECT [2, NULL]
  • SELECT * FROM a_view, when the view returns such an array. CREATE VIEW ... AS SELECT [1, NULL] AS a itself succeeds, and the error comes when you read the view

Fixes

Drop the NULLs, or replace them, before the array leaves the query.

SELECT ARRAY_AGG(x IGNORE NULLS ORDER BY x) AS a FROM UNNEST([1, NULL, 3]) AS x

IGNORE NULLS goes after the expression and before ORDER BY. It also works with DISTINCT x IGNORE NULLS. If every value in a group is NULL, the result for that group is NULL. For the LEFT JOIN form above, a customer with no orders gets NULL. Wrap it in IFNULL(..., []) to get an empty array.

FixQueryResult
IGNORE NULLSARRAY_AGG(x IGNORE NULLS)[1, 3]
filter before the aggregateARRAY_AGG(x) ... WHERE x IS NOT NULL[1, 3]
replace the NULLARRAY_AGG(IFNULL(x, 0))[1, 0, 3]
filter an ARRAY(SELECT ...) on the value it returnsARRAY(SELECT y FROM (SELECT IF(x > 1, x, NULL) AS y FROM UNNEST([1, 2]) AS x) WHERE y IS NOT NULL)[2]
keep the NULL inside a STRUCTARRAY_AGG(STRUCT(x AS v))[{v: 1}, {v: NULL}, {v: 3}]

The STRUCT form keeps every row. The NULL is the value of the field v, and the array elements are structs.

A filter has to test the value that goes into the array. This one does not:

SELECT ARRAY(SELECT IF(x > 1, x, NULL) FROM UNNEST([1, 2]) AS x WHERE x IS NOT NULL) AS a

It fails with the same message. x is never NULL here. The IF makes the NULL after the filter.

IGNORE NULLS is not available when ARRAY_AGG is a window function. ARRAY_AGG(x IGNORE NULLS) OVER () gives a different error:

Analytic function array_agg does not support IGNORE NULLS or RESPECT NULLS.

Queries that look wrong but run

The array is used inside the query. These all run on [1, NULL]: ARRAY_LENGTH returns 2, [1, NULL][OFFSET(1)] returns NULL, SELECT x FROM UNNEST([1, NULL]) AS x returns both rows, and SELECT COUNT(*) FROM (SELECT [1, NULL] AS a) returns 1. Even WITH t AS (SELECT [1, NULL] AS a) SELECT ARRAY_LENGTH(a) FROM t runs.

The NULLs are turned into text. TO_JSON_STRING([1, NULL]) returns [1,null], and ARRAY_TO_STRING(['a', NULL, 'b'], ',') returns a,b. STRING_AGG(x) over ['a', NULL, 'b'] returns a,b.

A NULL array. SPLIT(NULL, ',') returns NULL, and so do GENERATE_ARRAY(1, NULL) and REGEXP_EXTRACT_ALL(NULL, 'a'). A NULL array has no NULL element, so BigQuery returns it without an error. SPLIT('a,,b', ',') returns ["a","","b"], with an empty string in the middle and no NULL. The arrays above are not the cause of this error.

An array of structs. ARRAY(SELECT AS STRUCT x) FROM UNNEST([1, NULL]) AS x returns [{x: NULL}] for the second row. The struct is not NULL, so nothing is refused.

Different errors that look similar

MessageCause
Cannot construct array with element type ARRAY<INT64> because nested arrays are not supported at [1:46]ARRAY_CONCAT_AGG over an array of arrays
Analytic function array_agg does not support IGNORE NULLS or RESPECT NULLS.IGNORE NULLS on a window function

In Querylab.io

The editor does not mark the NULL as you type. BigQuery accepts the query in a dry run, and the error appears when the query runs.

After a failed run, the error bar offers a button that adds IGNORE NULLS to the ARRAY_AGG. It appears when the message names an output column and the query has one ARRAY_AGG with one argument and no IGNORE NULLS that makes that column. In a query with CTEs, subqueries or a set operation, there must be exactly one such call, because the message gives a column name and nothing that tells which block the call is in. There is no button when the ARRAY_AGG has OVER (...), because IGNORE NULLS is refused there. There is no button for an array literal, ARRAY(SELECT ...), ARRAY_CONCAT or JSON_VALUE_ARRAY. The fix there depends on where the NULL should go.

See NULL handling and Arrays.