# 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. Source: https://docs.querylab.io/bigquery-errors/array-cannot-have-a-null-element/ Updated: 2026-11-11 ```text 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 ```sql SELECT [1, NULL] AS a ``` ```text Array cannot have a null element; error in writing field a ``` The fix depends on where the `NULL` comes from: ```sql 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: ```sql SELECT STRUCT([1, NULL] AS a) AS s ``` ```text 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. ```sql 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: ```sql 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: ```text 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 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](https://docs.querylab.io/bigquery/null-handling/) and [Arrays](https://docs.querylab.io/bigquery/arrays/). ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Data types, NULLs and the array type](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#array_nulls) - [Aggregate functions, ARRAY_AGG](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#array_agg)