Menu
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 ?
447
To remove all null values from an array:Declare a results variable and set it to an empty array. Use the forEach() method to iterate over the array. Check if each element is not equal to null . If the condition is satisfied, push the element into the results array.
To convert an ARRAY into a set of rows, also known as "flattening," use the UNNEST operator. UNNEST takes an ARRAY and returns a table with a single row for each element in the ARRAY . Because UNNEST destroys the order of the ARRAY elements, you may wish to restore order to the table.
What are Structs and how are they used in BigQuery: A struct is a data type that has attributes in key-value pairs, just like a dictionary in Python. Within each record, multiple attributes have their own values. These attributes can either be referred to as keys or Struct columns.
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.
172
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With