BigQuery嵌套表数据提取：如何获取全部嵌套Name字段值

问题说明 #

一开始从UserDetails嵌套表提取数据时，用SELECT Details[OFFSET(0)].Name FROM UserDetails只能拿到第一个Name值，需要提取所有嵌套的Name。后来补充了表结构：UserDetails下有Users数组，每个Users元素包含Bio对象（含Name、Age、Address字段），共有4个Bio条目，想知道能不能用UNNEST来提取所有UserDetails.Users.Bio.Name的值。

先修正下你提供的表结构JSON（原JSON存在语法错误，比如对象间缺少逗号、部分Bio结构不完整），正确结构应该是这样：

"UserDetails": [{
  "Users": [
    {"Bio": {"Name": "Mark", "Age": 23, "Address": "ABC"}},
    {"Bio": {"Name": "John", "Age": 25, "Address": "DEF"}},
    {"Bio": {"Name": "Mark", "Age": 23, "Address": "ABC"}},
    {"Bio": {"Name": "Shane", "Age": 29, "Address": "MNO"}}
  ]
}]

解决方案：用UNNEST展开数组 #

UNNEST完全适合这个场景，它能把数组拆成多行，从而取出数组里所有元素的字段值。

具体查询语句 #

如果UserDetails是你的表名，且表中的Users是数组类型字段，直接用下面的查询就能拿到所有Name：

SELECT user.Bio.Name
FROM UserDetails,
UNNEST(UserDetails.Users) AS user

语句解释

• UNNEST(UserDetails.Users)把Users数组展开成单独的行，每一行对应一个Users元素；
• 接着通过user.Bio.Name就能获取每个Bio对象里的Name值，最终会返回全部4个名称。

如果你的表中UserDetails本身是一个数组字段（而非表名），则需要先展开外层数组：

SELECT user.Bio.Name
FROM your_table_name,
UNNEST(your_table_name.UserDetails) AS ud,
UNNEST(ud.Users) AS user

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