BigQuery SQL中CONCAT字符串与STRUCT<f0_ STRING>报错的解决方法

云原生阿岚

2026-7-20

解决BigQuery循环中STRUCT转STRING的错误问题

问题根源

你用SELECT * FROM UNNEST(vector)时，UNNEST输出的单个字符串会被自动包装成一个STRUCT（默认字段名为f0_），导致循环变量loop_variable_name是STRUCT类型，而非STRING，因此CONCAT函数和CAST转换都会报错。

两种解决方法

方法1：UNNEST时指定字段名，循环中引用该字段

给UNNEST的元素命名，循环里直接调用命名后的字段获取字符串值，同时注意动态创建表必须用EXECUTE IMMEDIATE执行动态SQL（原代码的CREATE TABLE写法不符合BigQuery规则）：

DECLARE vector ARRAY<STRING>;
DECLARE table_name STRING;
SET vector = ["AA","AB","AC"];

FOR loop_variable_name IN (SELECT val FROM UNNEST(vector) AS val)
DO
  SET table_name = CONCAT('mergingtask.preclean.linkedin', loop_variable_name.val);
  EXECUTE IMMEDIATE CONCAT('CREATE TABLE `', table_name, '` AS SELECT * FROM `mergingtask.preclean.preclean_data_sorted` WHERE last2 = "', loop_variable_name.val, '"');
END FOR;

方法2：直接访问STRUCT的默认字段`f0_`

如果不想修改UNNEST的查询语句，直接调用STRUCT的默认字段f0_获取字符串值，同样配合EXECUTE IMMEDIATE：

DECLARE vector ARRAY<STRING>;
DECLARE table_name STRING;
SET vector = ["AA","AB","AC"];

FOR loop_variable_name IN (SELECT * FROM UNNEST(vector))
DO
  SET table_name = CONCAT('mergingtask.preclean.linkedin', loop_variable_name.f0_);
  EXECUTE IMMEDIATE CONCAT('CREATE TABLE `', table_name, '` AS SELECT * FROM `mergingtask.preclean.preclean_data_sorted` WHERE last2 = "', loop_variable_name.f0_, '"');
END FOR;