BigQuery中Unnest整数列表报错，求关联客户ID的正确SQL实现

错误原因分析 #

报错提示Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRUCT<os STRING, product_ids STRING...>，说明你要UNNEST的目标字段要么是STRUCT类型（product_ids嵌套在某个结构体里），要么是字符串类型（而非真正的整数数组），不符合UNNEST对数组类型的要求。

分场景解决方案 #

场景1：product_ids嵌套在STRUCT字段中 #

如果product_ids是某个STRUCT字段的子字段（比如错误提示里的STRUCT包含os和product_ids），需要先提取STRUCT中的product_ids，再执行UNNEST：

SELECT 
  customer_id,
  pid AS product_id
FROM 
  my_table,
  UNNEST(your_struct_field.product_ids) AS pid

注意替换your_struct_field为实际的STRUCT字段名（比如表中存储该结构体的字段，如user_details）。如果STRUCT里的product_ids是字符串形式的数组，需先转成整数数组：

SELECT 
  customer_id,
  CAST(pid AS INT64) AS product_id
FROM 
  my_table,
  UNNEST(SPLIT(TRIM(your_struct_field.product_ids, '[]'), ',')) AS pid

场景2：product_ids是字符串类型（存储类似[123456,234567]的文本） #

如果product_ids字段本身是字符串而非BigQuery数组类型，需先将字符串转换为整数数组再UNNEST：

SELECT 
  customer_id,
  CAST(pid AS INT64) AS product_id
FROM 
  my_table,
  UNNEST(SPLIT(TRIM(product_ids, '[]'), ',')) AS pid

• TRIM(product_ids, '[]')：移除字符串前后的方括号
• SPLIT(..., ',')：按逗号分割成字符串数组
• CAST(pid AS INT64)：将分割后的字符串转为整数

场景3：确认字段类型并安全转换 #

若不确定字段类型，先执行以下SQL查看表结构和样本数据：

SELECT * FROM my_table LIMIT 5

如果product_ids是疑似数组的字符串，也可以用SAFE_CAST做安全转换，避免转换失败报错：

SELECT 
  customer_id,
  pid AS product_id
FROM 
  my_table,
  UNNEST(SAFE_CAST(product_ids AS ARRAY<INT64>)) AS pid

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