如何在BigQuery SQL中展开多层嵌套重复记录并解决查询报错

报错根因 #

你遇到的Table name "nhht" missing dataset报错，核心原因有两个：

1. 你把UNNEST生成的行级临时结果nhht、ohht当成了可查询的表放到子查询里，BigQuery无法识别这种引用逻辑。
2. 你原SQL写错了字段名：表结构里的字段是new_houses、old_houses，你写的是newhouses、oldhouses，也会引发解析异常。
   另外你原写法用的全是交叉连接（逗号分隔的FROM子句默认是CROSS JOIN），如果某类数组为空会直接丢失整行数据，也会产生大量重复结果，不符合去重要求。

实现需求的修改后查询 #

WITH
-- 提取table1中new_houses、old_houses下所有去重的house_type_id
all_house_ids AS (
  SELECT DISTINCT
    house_type_info.house_type_id
  FROM `table1` AS tab1,
  UNNEST(tab1.new_houses) AS nh,
  UNNEST(nh.house_type) AS nh_type
  UNION ALL
  SELECT DISTINCT
    house_type_info.house_type_id
  FROM `table1` AS tab1,
  UNNEST(tab1.old_houses) AS oh,
  UNNEST(oh.house_type) AS oh_type
),
-- 提取favorite_houses中house_type_id和对应door color的映射关系，去重避免多匹配
favorite_color_map AS (
  SELECT DISTINCT
    fmd.house_type_metadata_secure.house_type_id,
    fh.house_color_data.house_door_color
  FROM `table1` AS tab1,
  UNNEST(tab1.favorite_houses) AS fh,
  UNNEST(fh.house_info.house_type_data.house_type_metadata) AS fmd
)
-- 关联表2取基础字段，优先取收藏的门颜色
SELECT
  tab2.house_type_id,
  COALESCE(fcm.house_door_color, tab2.house_door_color) AS house_door_color,
  tab2.house_name,
  tab2.house_roof_color
FROM all_house_ids ids
LEFT JOIN `table2` AS tab2
  ON ids.house_type_id = tab2.house_type_id
LEFT JOIN favorite_color_map fcm
  ON tab2.house_type_id = fcm.house_type_id
-- 最终去重保证结果无重复
GROUP BY 1,2,3,4

逻辑说明 #

• 用CTE拆分不同逻辑模块，避免嵌套引用错误：
  • all_house_ids分别展开new_houses和old_houses数组，汇总所有去重的house_type_id
  • favorite_color_map单独展开favorite_houses数组，生成id和自定义门颜色的映射
• 关联时用COALESCE实现优先级逻辑：如果存在收藏的门颜色就取收藏值，否则取table2的默认值
• 最后用GROUP BY去重，避免数组展开产生的重复记录

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