BigQuery：提取嵌套重复记录指定字段并保留原结构

刚好之前我也遇到过类似的多层嵌套重复字段处理需求，给你一个能完美保留原结构的实现方法！

核心思路就是先拆分数组处理每个层级的字段，再重新聚合恢复结构，用到你提到的UNNEST和ARRAY_AGG组合，具体操作如下：

假设原表结构（模拟你的场景） #

先明确下我理解的表结构（如果和实际有出入，调整字段名即可）：

CREATE TABLE your_dataset.your_table (
  id STRING, -- 顶层唯一标识
  top_level_field INT64, -- 其他顶层字段
  DatiRighe ARRAY<STRUCT<
    riga_id STRING,
    importo FLOAT64,
    Nota STRING, -- 需要排除的字段
    DatiRigheDettaglio ARRAY<STRUCT<
      dettaglio_id STRING,
      quantita INT64,
      cod_iva STRING -- 需要排除的字段
    >>
  >>
);

实现SQL代码 #

SELECT
  -- 保留所有顶层字段（直接列出来，或者用* EXCEPT(...)排除不需要的顶层字段）
  id,
  top_level_field,
  -- 处理DatiRighe数组：拆解→处理字段→重新聚合
  ARRAY_AGG(STRUCT(
    -- 保留DatiRighe中除Nota外的所有字段，用* EXCEPT更高效（字段多的时候不用逐个列）
    dr.* EXCEPT(Nota),
    -- 处理嵌套的DatiRigheDettaglio子数组
    ARRAY_AGG(
      drd.* EXCEPT(cod_iva)
    ) AS DatiRigheDettaglio
  )) AS DatiRighe
FROM your_dataset.your_table
-- 拆解顶层的DatiRighe数组
LEFT JOIN UNNEST(DatiRighe) dr
-- 拆解DatiRighe内的DatiRigheDettaglio子数组
LEFT JOIN UNNEST(dr.DatiRigheDettaglio) drd
-- 按顶层唯一键分组，确保聚合后和原表行一一对应
GROUP BY id, top_level_field;

关键细节说明 #

• * EXCEPT(column)用法：这是BigQuery里非常实用的语法，能快速排除指定字段，不用手动列出所有需要保留的字段，尤其适合字段较多的场景；
• 层级对应聚合：先处理最内层的DatiRigheDettaglio数组，再处理外层的DatiRighe数组，顺序不能乱；
• 分组依据：必须用顶层的唯一标识字段分组（比如示例中的id和top_level_field），否则会导致行数据合并错误；
• 空数组兼容：用LEFT JOIN而不是JOIN，可以保证原表中为空的数组在结果中依然保留为空，不会丢失数据。

如果你的表结构和我模拟的略有不同，只需要调整字段名和层级的UNNEST/ARRAY_AGG位置即可，核心逻辑是通用的。

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