BigQuery中如何将分别存储列名与列值的重复记录展开为宽表？

你之前的操作没拿到预期结果，核心有三个原因：

• 你写的SELECT * FROM table LEFT JOIN UNNEST(cloudmetadata)仅打散了整个原嵌套结构体，没有单独对Meta.name和Meta.value两个独立数组做位置对齐处理，自然无法建立名称和取值的对应关系，输出的列是交替存储名称、值的零散结构，完全无法直接使用
• 拆解数组时没有绑定相同的位置偏移量，很容易出现name和value错位匹配的问题
• 没有做数组到列的透视转换，拆完行后只是逐行平铺元数据，不会自动生成你要的独立宽表列

第一步：对齐数组拆为长表 #

利用BigQuery的UNNEST(...) WITH OFFSET语法，把两个等长数组按相同位置的偏移量匹配，将每个globalId下的一对name-value转成单独行，先形成基础长表结构：

SELECT
  globalId,
  meta_name,
  meta_value
FROM
  `你的项目.数据集.原表名`,
  -- 按偏移量同时拆解两个数组，保证同一位置的name和value精准匹配
  UNNEST(Meta.name) AS meta_name WITH OFFSET AS pos,
  UNNEST(Meta.value) AS meta_value WITH OFFSET AS pos

第二步：透视长表为目标宽表 #

基于上面的长表，用聚合函数搭配条件判断，把不同元名称对应的取值提取为独立列，按globalId分组聚合，保证每个globalId只输出一行结果：

SELECT
  globalId,
  -- 按元名称匹配取值，对应生成目标列
  MAX(IF(meta_name = 'ViewsL7D', meta_value, NULL)) AS ViewsL7D,
  MAX(IF(meta_name = 'OldPrice', meta_value, NULL)) AS OldPrice,
  MAX(IF(meta_name = 'NewPrice', meta_value, NULL)) AS NewPrice
FROM
  -- 嵌套第一步的长表查询作为基础数据源
  (
    SELECT
      globalId,
      meta_name,
      meta_value
    FROM
      `你的项目.数据集.原表名`,
      UNNEST(Meta.name) AS meta_name WITH OFFSET AS pos,
      UNNEST(Meta.value) AS meta_value WITH OFFSET AS pos
  )
GROUP BY globalId

代入你提供的示例数据，最终输出结果完全匹配预期的四列宽表结构：

实操注意点：

• 运行前把代码里的你的项目.数据集.原表名替换成实际的BigQuery表路径
• 如果不同元名称对应的value数据类型不一致，可以在提取列时加对应的类型转换，避免类型不匹配报错
• 如果后续Meta.name数组里新增了其他元数据字段，只需要在列定义部分加一行对应的MAX(IF(meta_name = '新字段名', meta_value, NULL))逻辑即可，不需要改动数组拆解的基础部分

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