如何在BigQuery中展平包含重复字段的RECORD类型字段？

背景信息 #

我的表包含一个嵌套对象（record），该对象带有4个数组（repeated）类型的嵌套属性：

BM.HISTORY.AutoBill.BaseRate
BM.HISTORY.AutoBill.Substatus
BM.HISTORY.AutoBill.Services
BM.HISTORY.AutoBill.Conditions

每个属性的数据结构如下：

[
 {Label: "Completed....", Amount: 34.3, Quantity: 2}, 
 {Label: "Completed....", Amount: 34.3, Quantity: 2},
 ...
]

期望输出 #

需要提取出完全扁平的CSV格式数据，示例如下：

OrderId  StartWindow                 Type       Label                      Quantity Amount
123      2022-07-17 13:07:00 UTC     BaseRate   Completed @ 100%           0        82.6
123      2022-07-17 13:07:00 UTC     Services   service 1                  1        16.1323
123      2022-07-17 13:07:00 UTC     Services   service 2                  1        5
123      2022-07-17 13:07:00 UTC     Conditions 10% Time Window Premium    0.826    8.26

234      2022-07-17 13:07:00 UTC     BaseRate   Completed @ 100%           0        3.6
234      2022-07-17 13:07:00 UTC     Services   service 1                  1        16.1323
234      2022-07-17 13:07:00 UTC     Services   service 2                  1        5
234      2022-07-17 13:07:00 UTC     Conditions 10% Time Window Premium    0.826    8.26

规则说明：

• StartWindow是表中的普通字段；
• Type对应上述4个属性的后缀名称（如BaseRate、Services等）；
• 每个数组内的对象需展平为单独行，所有数据按行堆叠。

尝试的SQL及问题 #

我使用以下SQL尝试展平，但结果未完全达到要求，数组结构仍未被展开：

SELECT
  Id as OrderId, StartWindow, Billing
FROM
  `xxx.xxxx.xxDB ADDRESSxxx`,
  UNNEST([ 
      (BM.HISTORY.BILL.BaseRate,'BASE RATE'),
      (BM.HISTORY.BILL.Substatus,'SUBSTATUS'),
      (BM.HISTORY.BILL.Services,'SERVICES'),
      (BM.HISTORY.BILL.Conditions,'CONDITIONS')
    ]
  ) as Billing
LIMIT 10

解决方案 #

需要先关联每个数组字段与对应的Type标签，再对每个数组执行二次UNNEST操作，彻底展平层级。正确SQL如下：

SELECT
  Id AS OrderId,
  StartWindow,
  Type,
  item.Label,
  item.Quantity,
  item.Amount
FROM
  `xxx.xxxx.xxDB ADDRESSxxx`,
  -- 关联数组字段与对应Type标签
  UNNEST([
    STRUCT(BM.HISTORY.AutoBill.BaseRate AS items, 'BaseRate' AS Type),
    STRUCT(BM.HISTORY.AutoBill.Substatus AS items, 'Substatus' AS Type),
    STRUCT(BM.HISTORY.AutoBill.Services AS items, 'Services' AS Type),
    STRUCT(BM.HISTORY.AutoBill.Conditions AS items, 'Conditions' AS Type)
  ]),
  -- 展平每个数组内的对象
  UNNEST(items) AS item
-- 可选：按订单ID和类型排序，使结果更规整
ORDER BY OrderId, Type

逻辑说明 #

1. 用STRUCT将每个数组字段和对应的Type标签打包，通过第一次UNNEST生成4条中间记录（每个Type对应一条）；
2. 对每条中间记录中的items数组执行第二次UNNEST，将数组内的每个对象拆分为单独行；
3. 选择所需字段后，得到完全扁平的结果，直接导出即可生成目标CSV格式。

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