41

ARRAY_AGG aggregate function includes NULLs in the arrays it builds. When such arrays are part of query result, query fails with error:

Array cannot have a null element; error in writing field

i.e. the following query demonstrates it:

#standardSQL
SELECT ARRAY_AGG(x) FROM UNNEST([1,NULL,2,3]) x

How can we solve it ?

Mosha Pasumansky
  • 13,206
  • 5
  • 32
  • 55

3 Answers3

61

Glad you asked! BigQuery supports IGNORE NULLS and RESPECT NULLS modifiers in some of the aggregate functions, including ARRAY_AGG, so your query becomes

#standardSQL
SELECT ARRAY_AGG(x IGNORE NULLS) FROM UNNEST([1,NULL,2,3]) x

and it passes producing [1,2,3]. More details are in the documentation.

Mosha Pasumansky
  • 13,206
  • 5
  • 32
  • 55
  • 1
    Why didn't I see this in the [release notes](https://cloud.google.com/bigquery/docs/release-notes)? I thought that's where the BigQuery team announces new features. – Elliott Brossard Mar 03 '17 at 18:14
  • It made to the release notes, but they are not yet published. – Mosha Pasumansky Mar 03 '17 at 18:16
  • why does `ARRAY_AGG` work and show nulls for some queries but for others it says `Array cannot have a null element; error in writing field x`? – cryanbhu Sep 11 '20 at 09:50
  • ARRAYs can have NULLs when they are passed inside the query, but the ARRAYs in the final result of the query, which are written to a table have limitation of not allowing NULL elements. – Mosha Pasumansky Sep 16 '20 at 20:12
6

Another interesting use-case would be if you do not want to lose that NULL elements but rather want to substitute it with some default value. For example -999

Below will do this 

#standardSQL
SELECT ARRAY_AGG(IFNULL(x,-999)) FROM UNNEST([1,NULL,2,3]) x

And in case if you want distinct elements only - 

#standardSQL
SELECT ARRAY_AGG(DISTINCT IFNULL(x,-999)) FROM UNNEST([1,NULL,2,3,1,NULL]) x
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
4

Complementing @Mikhail Berlyant's answer, sometimes you want to keep the NULL values somehow but can't use a placeholder. For example, if you want a boolean array with NULLs, where you can't substitute NULL for true/false.

A nice workaround is wrapping the values inside a STRUCT, then constructing the array:

#standardSQL
SELECT ARRAY_AGG(STRUCT(x)) AS struct_array FROM UNNEST([1,NULL,2,3]) x
arredond
  • 569
  • 6
  • 8