BigQuery查询含字典列表的表时如何提取指定键值且避免行重复

你的行数膨胀问题是UNNEST操作导致的：原表每行的customized_field是长度为25的数组，UNNEST后会把1行拆为25行，和原表做笛卡尔积，CASE语句仅在匹配对应id的行返回数值，其余行返回null，所以才会出现大量重复的数值对。

最优解决方案 #

不需要在FROM层做UNNEST，直接通过行内子查询提取对应id的value，不会拆分原表行数，写法如下：

SELECT
  -- 从当前行的customized_field数组中筛选id为Bob的元素，取其value
  (SELECT h.data.value FROM UNNEST(customized_field) AS h WHERE h.data.id = 'Bob') AS Bob,
  -- 同理提取Mary的value
  (SELECT h.data.value FROM UNNEST(customized_field) AS h WHERE h.data.id = 'Mary') AS Mary
FROM `你的项目名.你的库名.你的表名`

备选方案（保留外层UNNEST写法） #

如果需要保留外层UNNEST的写法，可通过聚合把拆分的行合并回来：

SELECT
  MAX(CASE WHEN h.data.id = 'Bob' THEN h.data.value END) AS Bob,
  MAX(CASE WHEN h.data.id = 'Mary' THEN h.data.value END) AS Mary
FROM `你的项目名.你的库名.你的表名`,
UNNEST(customized_field) AS h
-- 按原表的唯一行标识分组，确保原表每行最终只返回1行结果
GROUP BY `你的项目名.你的库名.你的表名`.primary_key -- 替换为你原表的主键字段，若无主键可替换为所有不需要展开的原表字段

注意：你原SQL中写的h.value.value是字段引用错误，按照你给出的结构，value字段在data节点下，应该写为h.data.value。

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