如何在BigQuery中更新四层嵌套字段？

你的语法错误出在REPLACE操作里连续写了两个独立的SELECT AS STRUCT语句，BigQuery不支持这种写法，需要把多个字段的替换合并到同一个STRUCT操作中。同时因为目标字段嵌套在多层STRUCT里，得逐层替换对应的结构才能正确更新。

正确的更新语句 #

UPDATE `project.dataset.table`
SET event = 
  (SELECT AS STRUCT event.* REPLACE(
    (SELECT AS STRUCT event.group.* REPLACE(
      (SELECT AS STRUCT event.group.details.* REPLACE(
        ARRAY(
          SELECT AS STRUCT attributes.* REPLACE(
            'some name' AS name,
            'some value' AS value
          )
          FROM UNNEST(event.group.details.attributes) AS attributes
        ) AS attributes
      )) AS details
    )) AS group
  ))
WHERE TRUE;

代码逻辑说明 #

1. 逐层嵌套替换：从最外层的event结构体开始，依次向内替换group、details，最终定位到attributes数组，确保每一层的原有字段都被保留，只更新目标内容。
2. 数组元素更新：通过UNNEST展开数组，遍历每个attributes元素，用REPLACE同时修改name和value字段，再重新组合成数组。

可选：更新特定条件的数组元素 #

如果不需要更新数组里的所有元素，可在UNNEST后的查询中添加过滤条件，比如只更新原name为"old name"的元素：

ARRAY(
  SELECT AS STRUCT attributes.* REPLACE(
    'some name' AS name,
    'some value' AS value
  )
  FROM UNNEST(event.group.details.attributes) AS attributes
  WHERE attributes.name = 'old name'
) AS attributes

内容的提问来源于stack exchange，提问作者Kulasangar