BigQuery中对数组字段使用contains_substr的实现问题（GCP账单场景）

问题根源 #

你之前的查询错误是因为UNNEST(credits) AS cr后，cr已经是数组中的单个credits结构体对象，不需要再加credits.前缀，直接访问cr.type和cr.amount即可。另外，直接用逗号连接UNNEST会将原表的每一行展开成多行（每个credits对应一行），如果需要保留原表结构并计算符合条件的credits总额，需要配合聚合函数使用。

可行方案1：计算每行的commit credits总额（保留原表行数） #

使用LEFT JOIN UNNEST配合SUM()聚合，确保原表每一行只输出一次，同时计算该行所有符合条件的credits金额总和：

SELECT 
  s.*,
  SUM(CASE WHEN CONTAINS_SUBSTR(cr.type, 'commit') THEN cr.amount ELSE 0 END) AS CUD_credits,
  CASE WHEN CONTAINS_SUBSTR(s.sku.description, 'commit') THEN s.cost ELSE 0 END AS commit_cost
FROM `gcp_billing_export_resource_v1_017XXX_969XXX_BE9XXX` s
LEFT JOIN UNNEST(s.credits) AS cr
WHERE _PARTITIONDATE > DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)
GROUP BY ALL

注：GROUP BY ALL是BigQuery专属语法，会自动将原表所有非聚合字段纳入分组，无需手动罗列；如果需要兼容标准SQL，可手动列出原表所有字段。

可行方案2：展开credits行（查看单条credit详情） #

如果需要查看每一条符合条件的credits记录（原表行会被展开为多条），直接修正字段访问方式即可：

SELECT 
  s.*,
  CASE WHEN CONTAINS_SUBSTR(cr.type, 'commit') THEN cr.amount ELSE 0 END AS CUD_credits,
  CASE WHEN CONTAINS_SUBSTR(s.sku.description, 'commit') THEN s.cost ELSE 0 END AS commit_cost
FROM `gcp_billing_export_resource_v1_017XXX_969XXX_BE9XXX` s,
UNNEST(s.credits) AS cr
WHERE _PARTITIONDATE > DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)

这里去掉了错误的cr.credits.前缀，直接使用cr.type和cr.amount，解决字段不存在的报错。

视图创建示例 #

如果要将结果保存为视图，只需将上述查询包裹在视图创建语句中：

CREATE VIEW `your-project.your-dataset.billing_commit_view` AS
-- 插入上述任意方案的SELECT语句

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