BigQuery FOR循环内数组函数报错，单独运行正常问题求助

错误原因 #

你遇到的ARRAY_LENGTH参数类型错误，核心问题是混淆了字段路径字符串和实际表字段：

• 从INFORMATION_SCHEMA.COLUMN_FIELD_PATHS获取的field_path是字符串类型（比如"array_col"或"struct_col.nested_array"），不是表中的数组字段本身。
• 直接在SELECT语句中使用field.field_path时，BigQuery会把它当作普通字符串处理，而非动态引用表中的数组字段，导致ARRAY_LENGTH接收到字符串参数，触发类型不匹配错误。

修正方案 #

使用EXECUTE IMMEDIATE实现动态SQL，将field_path字符串作为字段名动态插入查询语句中：

FOR field IN (
  SELECT
    field_path, data_type
  FROM
    `project.dataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
  WHERE
    table_name = 'table_1'
    AND data_type LIKE 'ARRAY%' 
) DO
  EXECUTE IMMEDIATE """
    SELECT
      ARRAY_LENGTH(`""" || field.field_path || """`) AS s3,
      IF(REGEXP_CONTAINS(ARRAY_TO_STRING(`""" || field.field_path || """`, ','), r'.*?\\,\\s\\,.*?'), 1, 0) AS b1,
      IF(REGEXP_CONTAINS(ARRAY_TO_STRING(`""" || field.field_path || """`, ','), r'^\\s\\,.*?'), 1, 0) AS b2,
      IF(REGEXP_CONTAINS(ARRAY_TO_STRING(`""" || field.field_path || """`, ','), r'.*?\\,\\s$'), 1, 0) AS b3
    FROM
      `project.dataset.table_1`
  """;
END FOR;

关键说明 #

• 动态字段引用：通过字符串拼接，将field.field_path作为字段名用反引号包裹，确保BigQuery识别为表中的实际字段。
• 正则转义：由于在字符串拼接中，正则表达式的反斜杠需要双重转义（\\），否则会被当作字符串转义符处理。
• EXECUTE IMMEDIATE作用：BigQuery的FOR循环中无法直接动态引用字段，必须通过该语句执行拼接后的动态SQL。

验证逻辑 #

单独运行DO块内的查询时，你应该是手动替换了field.field_path为实际字段名（比如array_col），此时BigQuery能正确识别为数组字段，所以查询正常。动态循环时必须用EXECUTE IMMEDIATE来实现同样的字段引用逻辑。

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