CODEWITHSUNDEEP
sqlgoogle-bigquery
Andrew Cassidy
Andrew Cassidy

Reputation: 2998

Unnesting and reforming a big query array seems to destroy null

SELECT ARRAY(SELECT foo FROM UNNEST(CAST(NULL as ARRAY<STRING>)) as foo) is null

returns false

vs. 

SELECT CAST(NULL as ARRAY<STRING>) is null

returns true

My specific situation is that I'd like to sort an array using 

SELECT ARRAY(SELECT foo from UNNEST(bar) as foo Order by foo) as arr

and keep the array as null if it was originally null. I'm open to alternatives for sorting the array but preserving null (meaning I'm not hung up on my implementation).

Upvotes: 1

Views: 751

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

An empty array is different from a NULL with an array type. An empty array is an array with no elements. That is what you get from the UNNEST(). So, the comparison to NULL is false.

The second is returning NULL typed as an array. This is equivalent to NULL, so the comparison is true.

Upvotes: 2

Related Questions