BigQuery：如何用原生语句实现重复数组记录转重复记录

当然可以用BigQuery原生SQL搞定这个转换，完全不需要依赖临时UDF，这样就能顺利创建视图啦！

核心思路是利用UNNEST ... WITH OFFSET获取每个数组元素的索引位置，然后把相同索引的字段配对，最后再聚合回数组结构。下面给你针对需求的具体实现：

基础示例（对应你给出的JSON结构） #

假设你的表名为your_table，包含一个名为data的RECORD字段（里面有createdBy和fileName两个REPEATED数组），可以用以下SQL：

SELECT
  -- 按索引配对后聚合为Repeated Record
  ARRAY_AGG(STRUCT(
    createdBy,
    fileName
  )) AS paired_records,
  -- 保留原始表的其他字段（如果需要的话）
  other_table_columns
FROM your_table,
-- 展开每个数组并带上索引
UNNEST(data.createdBy) AS createdBy WITH OFFSET AS pos,
UNNEST(data.fileName) AS fileName WITH OFFSET AS pos2
-- 匹配相同索引的元素
WHERE pos = pos2
-- 按原始记录的唯一标识分组，确保每条原始记录对应自己的重组数组
GROUP BY your_table.id, other_table_columns

扩展到多个字段的情况 #

如果你的实际数据有更多固定键的数组（比如fileSize、uploadTime等），只需要继续添加UNNEST ... WITH OFFSET，并在WHERE条件里保证所有索引一致：

SELECT
  ARRAY_AGG(STRUCT(
    createdBy,
    fileName,
    fileSize,
    uploadTime
  )) AS paired_records
FROM your_table,
UNNEST(data.createdBy) AS createdBy WITH OFFSET AS pos,
UNNEST(data.fileName) AS fileName WITH OFFSET AS pos2,
UNNEST(data.fileSize) AS fileSize WITH OFFSET AS pos3,
UNNEST(data.uploadTime) AS uploadTime WITH OFFSET AS pos4
WHERE pos = pos2 AND pos = pos3 AND pos = pos4
GROUP BY your_table.id

处理特殊情况 #

• 数组长度为0：如果所有数组都是空的，ARRAY_AGG会返回空数组，完全符合需求；
• 数组长度不一致：如果某个数组长度和其他不匹配，WHERE条件会自动过滤掉不对应的索引，最终只保留所有数组都存在对应元素的位置（如果你需要保留不匹配的情况，可以改用FULL JOIN并处理NULL值，但按你的需求应该不需要）。

这个方案完全使用BigQuery原生语法，没有临时函数，可以直接用来创建视图，完美解决你之前遇到的视图限制问题！

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