Big query update struct to null in repeated field

Question

In Google bigquery, I'm trying to do an update on a repeated field.

For comparison, this works (or at least is flagged as valid), but of course isn't actually updating the field.

UPDATE my.table t
SET my_field = ARRAY(
  SELECT AS STRUCT g.foo, g.bar, g.struct_to_set_null
  FROM unnest(t.groups) as g
), ... FROM ... etc

Setting struct_to_set_null to null gives an error:

UPDATE my.table t
SET my_field = ARRAY(
  SELECT AS STRUCT g.foo, g.bar, null as struct_to_set_null
  FROM unnest(t.groups) as g
), ... FROM ... etc

Value of type ARRAY<STRUCT<... (really long and cut off) cannot be assigned to groups, which has type <ARRAY,STRUCT<... (same, really long, cut off)

I can see that the field in question is of type RECORD and NULLABLE, so I would think setting it to null is allowed. Is there a trick to getting this to work?

Answer 1

Repeated is an Array type, so it cannot be set to NULL.

Currently, BigQuery has two following limitations with respect to NULLs and ARRAYs:

• BigQuery raises an error if query result has ARRAYs which contain NULL elements, although such ARRAYs can be used inside the query.
• BigQuery translates NULL ARRAY into empty ARRAY in the query result, although inside the query NULL and empty ARRAYs are two distinct values.

Answer 2

The problem is that BigQuery isn't inferring the type of the struct field just from the NULL literal; you need to be a bit more explicit. Here is an example:

CREATE TABLE tmp_elliottb.UpdateTable (
  my_field ARRAY<STRUCT<foo INT64, bar INT64, struct_to_set_null STRUCT<x STRING, y BOOL, z INT64>>>
);

UPDATE tmp_elliottb.UpdateTable
SET my_field = ARRAY(
  SELECT AS STRUCT foo, bar, NULL AS struct_to_set_null FROM UNNEST(my_field)
)
WHERE true;

This gives me:

Value of type ARRAY<STRUCT<foo INT64, bar INT64, struct_to_set_null INT64>> cannot be assigned to my_field, which has type ARRAY<STRUCT<foo INT64, bar INT64, struct_to_set_null STRUCT<x STRING, y BOOL, z INT64>>> at [4:16]

What I can do instead is to use an IF expression that produces NULL, but has struct_to_set_null on one of the branches in order to force the output type that I want:

UPDATE tmp_elliottb.UpdateTable
SET my_field = ARRAY(
  SELECT AS STRUCT
    foo, bar,
    IF(false, struct_to_set_null, NULL) AS struct_to_set_null 
  FROM UNNEST(my_field)
)
WHERE true;

Or, alternatively, I can use SELECT * REPLACE:

UPDATE tmp_elliottb.UpdateTable
SET my_field = ARRAY(
  SELECT AS STRUCT * REPLACE (IF(false, struct_to_set_null, NULL) AS struct_to_set_null )
  FROM UNNEST(my_field)
)
WHERE true;