BigQuery - Nested and Repeated not allowing to insert Null values

Question

I have a field with mutiplt Nested and Repeated fields inside it. It is not allowing me to Insert null values or null array for any child field with RECORD data type.

Is this a limitation or is there any workaround to this?

Answer 1

Just the question I've been looking for.. with no answer

Luckily, I've managed to find this question which address the same issue - only on update statement

The solution there works but it's a dirty hack - put the null inside an expression which address a field with the target type

IF(false, struct_to_set_null, NULL)

Where struct_to_set_null is the actual field name from your table

Unfortunately, it didn't work on my scenario because I used an inline select statement that is not on the same scope as my table

Instead, I simply used cast function (thanks to this answer). The downside is that I ended up with pretty much complex expressions such as:

# this represents a repeated record with a single field of type string
cast(null as Array<struct<STRING>>)

# same thing but with multiple values
cast(null as Array<struct<STRING, INTEGER>>)

On the bright side - it will always work + it's seems more clear and straight-forward (at least for me)

Hope this post will help someone out there