当向重复记录添加新元素时，在bigquery中保留空记录

Question

我在bigquery中有一个重复记录类型的字段，存储这个json:

"users": [{
"key": "name",
"value": {
"string_value": foo,
"int_value": null,
"float_value": null,
"double_value": null
}
}, {
"key": "age",
"value": {
"string_value": null,
"int_value": "477",
"float_value": null,
"double_value": null
}
}

我使用UNNEST查询这些字段，就像这样:

select * from myTable t,
unnest(users) as name, 
unnest(users) as age, 
WHERE name.key = 'name' 
AND age.key = 'age'

我的问题是我添加了第三个字段

"users": [{
"key": "name",
"value": {
"string_value": foo,
"int_value": null,
"float_value": null,
"double_value": null
}
}, {
"key": "age",
"value": {
"string_value": null,
"int_value": "477",
"float_value": null,
"double_value": null
}
},
{
"key": "lastName",
"value": {
"string_value": Johnson,
"int_value": null,
"float_value": null,
"double_value": null
}
}

姓氏字段以前不存在，所以我使用"historical"现在，即使使用左连接，也会从查询中排除仅包含前2个字段的行。

select * from myTable t,
unnest(users) as name, 
unnest(users) as age
left join unnest (users) as lastName 
WHERE name.key = 'name' 
AND age.key = 'age'
AND lastName.key = 'lastName'

这只返回包含3个字段的行(即使是左连接)，并且在我将新元素添加到重复字段之前，我丢失了所有数据。如何保持空行?

为了澄清，我的查询应该返回这样的东西(现在我没有得到第一个):

John, 12, null
Juan, 14, Perez

Answer 1

我猜你是在BQ中查询Firebase数据。

首先，您不应该为user结构中的每个字段添加新的连接。相反，您应该在数组中搜索键，然后在value结构中获得相关字段。

select 
(select value.string_value from unnest(users) where key = 'name') as user,
(select value.int_value from unnest(users) where key = 'age') as age,
(select value.string_value from unnest(users) where key = 'lastName') as lastName,
from myTable 

上面，我对姓名和姓氏使用字符串值，对年龄字段使用int值。请根据您的数据调整这些。