Unnest 2 levels大型查询数组

Question

考虑一个具有以下模式的BQ表

name
day
day.purchase  

和值

name     day              day.purchase
John    [1,2]  [ ['Coke', 'Fanta'], ['Pepsi', 'Fanta', 'Water'] ]

如何取消两个级别的嵌套，以便获得完整的表？

John 1 'Coke'
John 1 'Fanta'
John 2 'Pepsi'
John 2 'Fanta'
John 2 'Water'

Answer 1

如果我正确地获得了您的模式和示例数据，那么可以这样做：

select name, id as day, purchase
from yourtable t, t.day, day.purchase      

如果应用于问题中的样本数据，则输出为

​

​

Answer 2

对于所描述的数据模型，您只需使用：

select n.name, the_day, the_purchase
from names n cross join
     unnest(n.day) the_day cross join
     unnest(the_day.purchase) the_purchase;

对于所示的数据，我认为您有两个单独的列：

select n.name, day, purchase
from names n cross join
     unnest(n.days) day with offset nd join
     unnest(n.purchases) purchase with offset np
     on nd = np