Solved: BigQuery - Array cannot have a null element error in writing field ids

When trying to aggregate a group into an ARRAY, if there are NULL values in the group, It throws the below error:

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

Example:

Here's how my table looks like

Query:
SELECT name, ARRAY_AGG(DISTINCT order_id) AS ids
FROM table GROUP BY name

Since some of the order ids are null, it throws the below error message:

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

Solution:

You want to use the IGNORE NULLS optional clause. What this clause does is to exclude NULL values from the result of the array creation

We can perform a query with the ARRAY_AGG() function such as this one:

SELECT name, ARRAY_AGG(order_id IGNORE NULLS) as order_ids
FROM `PROJECT.DATASET.TABLE`
GROUP BY name
ORDER BY name

In order to obtain the following results:

Mike-Barn

posted on 21 Mar 19