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.
| Query | Where the NULL comes from |
|---|---|
SELECT [1, NULL] AS a | the array literal |
SELECT ARRAY_AGG(x) AS a FROM UNNEST([1, NULL, 3]) AS x | a 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 x | SAFE_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.id | a row of u with no match in o |
SELECT ARRAY(SELECT x FROM UNNEST([1, NULL, 3]) AS x) AS a | the subquery returns a NULL row |
SELECT ARRAY_CONCAT([1], [NULL]) AS a | a NULL in an input array |
SELECT ARRAY_REVERSE([1, NULL]) AS a | the same |
SELECT JSON_VALUE_ARRAY(JSON '{"k":["x",null]}', '$.k') AS b | a JSON null in the array |
SELECT ARRAY_AGG(x) OVER () AS a FROM UNNEST([1, NULL, 3]) AS x | the 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
SELECTthat returns the array SELECT * FROM (SELECT [1, NULL] AS a)WITH t AS (SELECT [1, NULL] AS a) SELECT a FROM tCREATE TABLE ... AS SELECT [1, NULL] AS aINSERT INTO t SELECT [2, NULL]SELECT * FROM a_view, when the view returns such an array.CREATE VIEW ... AS SELECT [1, NULL] AS aitself 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.
| Fix | Query | Result |
|---|---|---|
IGNORE NULLS | ARRAY_AGG(x IGNORE NULLS) | [1, 3] |
| filter before the aggregate | ARRAY_AGG(x) ... WHERE x IS NOT NULL | [1, 3] |
| replace the NULL | ARRAY_AGG(IFNULL(x, 0)) | [1, 0, 3] |
filter an ARRAY(SELECT ...) on the value it returns | ARRAY(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 STRUCT | ARRAY_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
| Message | Cause |
|---|---|
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.