如何在BigQuery中展平两个重复struct列并转置其中一列关联输出

这个需求完全可以通过BigQuery Standard SQL实现，分两种场景调整代码即可：

场景1：提前知道所有person的name枚举值（推荐，性能最优） #

如果你的业务中person的姓名是固定的（比如示例中的3个人名），可以直接写死匹配规则，查询语句如下：

SELECT
  -- 从公共person数组中提取对应人名的职业作为单独列
  (SELECT occupation FROM UNNEST(person) WHERE name = 'John Cena') AS `John Cena`,
  (SELECT occupation FROM UNNEST(person) WHERE name = 'Steve Austin') AS `Steve Austin`,
  (SELECT occupation FROM UNNEST(person) WHERE name = 'Vince McMahon') AS `Vince McMahon`,
  s.title,
  s.time
FROM `project.dataset.table`,
-- 展平show列，每行对应一个show条目
UNNEST(show) s

逻辑说明：UNNEST(show) 会把每条原记录的show数组拆成多行，而每条原记录对应的person数组是公共的，所以拆出来的所有show行都可以直接从person数组里匹配到对应的职业值，刚好符合你要的输出效果。

场景2：person的name不固定，需要动态生成列 #

如果person的姓名是动态变化的，没法提前枚举，就需要用动态SQL自动生成查询逻辑：

DECLARE column_list STRING;

-- 先统计所有去重的person姓名，生成对应的列查询语句
SET column_list = (
  SELECT STRING_AGG(
    FORMAT("(SELECT occupation FROM UNNEST(person) WHERE name = '%s') AS `%s`", name, name),
    ', '
  )
  FROM (
    SELECT DISTINCT p.name
    FROM `project.dataset.table`,
    UNNEST(person) p
  )
);

-- 拼接完整查询语句并执行
EXECUTE IMMEDIATE FORMAT("""
  SELECT %s, s.title, s.time
  FROM `project.dataset.table`,
  UNNEST(show) s
""", column_list);

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