大查询将重复字段中的结构更新为 null

在 Google bigquery 中，我试图对重复字段进行更新。

为了进行比较，这是可行的（或者至少被标记为有效），但当然实际上并没有更新该字段。

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为 null 会出现错误：

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)

我可以看到有问题的字段是类型RECORD and NULLABLE，所以我想将其设置为null被允许。有什么技巧可以让它发挥作用吗？

问题在于 BigQuery 不能仅从 NULL 文字推断结构字段的类型；你需要更明确一点。这是一个例子：

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;

这给了我：

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]

我可以做的是使用IF产生的表达式NULL，但是有struct_to_set_null在其中一个分支上，以强制我想要的输出类型：

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;

或者，我也可以使用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;