如何从BigQuery中提取嵌套数据并转换为扁平化单行格式

你原有SQL的问题在于没有取用UNNEST后返回的单条嵌套元素，而是仍选择了完整的title_localized数组，导致没有实现打平效果。

正确实现语句 #

SELECT
  -- 提取UNNEST后单条记录的三个字段
  test.text AS `title_localized.text`,
  test.language AS `title_localized.language`,
  test.truncated AS `title_localized.truncated`,
  publication_date
FROM
  `patents-public-data.patents.publications`,
  UNNEST(title_localized) AS test

如果需要和你示例结果一致的连续自增行号，可以加上窗口函数ROW_NUMBER()：

SELECT
  ROW_NUMBER() OVER() AS Row,
  test.text AS `title_localized.text`,
  test.language AS `title_localized.language`,
  test.truncated AS `title_localized.truncated`,
  publication_date
FROM
  `patents-public-data.patents.publications`,
  UNNEST(title_localized) AS test

原理说明 #

• BigQuery中使用逗号连接表和UNNEST(数组字段)属于交叉连接，会将数组中的每一个元素和父行的其他字段（此处为publication_date）自动关联，实现一行拆分为多行的效果，正好匹配你需要的每个语言版本都携带对应发布日期的需求
• 你需要的三个嵌套属性都存放在UNNEST后返回的test对象中，直接提取对应属性即可得到打平后的单行数据

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