BigQuery中含嵌套Struct的宽表转长表问题求助

我来帮你搞定这个带嵌套STRUCT的宽表转长表需求！你之前的方法只处理了顶层的键值对，没深入解析嵌套的STRUCT结构，所以没法得到t1_o_fga这种完整的metric路径。下面是可以直接复现的解决方案：

可运行的BigQuery代码 #

WITH wide_stats AS (
  SELECT 'joe' name, 'bills' team, struct(struct(7 as fga) as o, struct(8 as fga) as d) as t1, struct(struct(3 as fga) as o, struct(9 as fga) as d) as t2, struct(3 as pts, 9 as ast, 5 as reb) as t3, 7 tov, 3 blk 
  UNION ALL 
  SELECT 'nick' name, 'jets' team, struct(struct(12 as fga) as o, struct(13 as fga) as d) as t1, struct(struct(15 as fga) as o, struct(22 as fga) as d) as t2, struct(13 as pts, 5 as ast, 15 as reb) as t3, 75 tov, 23 blk 
)
SELECT 
  name,
  team,
  -- 将嵌套路径的点替换为下划线，生成清晰的metric名称
  REPLACE(metric_path, '.', '_') AS metric,
  SAFE_CAST(value AS FLOAT64) AS value
FROM wide_stats,
UNNEST(
  -- 提取所有层级的键值对，匹配"嵌套路径":值的格式
  REGEXP_EXTRACT_ALL(TO_JSON_STRING(wide_stats), r'"([^"]+)":([^,}]+)')
) AS kv_pair,
-- 拆分键值对为路径和值，处理字符串引号
UNNEST([STRUCT(
  SPLIT(kv_pair, ':')[OFFSET(0)] AS metric_path,
  TRIM(SPLIT(kv_pair, ':')[OFFSET(1)], '"') AS value
)]) AS kv
WHERE 
  -- 排除不需要展开的name和team列
  metric_path NOT IN ('name', 'team')
ORDER BY name, metric;

核心思路说明 #

1. 完整提取嵌套键值：用REGEXP_EXTRACT_ALL配合正则r'"([^"]+)":([^,}]+)'，直接从整行的JSON字符串里提取所有层级的键值对（包括嵌套STRUCT的底层字段，比如t1.o.fga），而不是只拆分顶层的键。
2. 处理键值对格式：拆分每个匹配到的键值对，去掉值两端的引号（适配字符串类型的值，数字类型也不受影响）。
3. 格式化metric名称：把嵌套路径里的.替换成_，得到你需要的t1_o_fga这种清晰的命名格式。
4. 过滤无关列：排除name和team，只保留需要展开的指标字段。

运行这段代码后，就能得到你想要的输出格式，行序可以根据需求调整，metric命名也符合表意清晰的要求。

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