如何在bigquery标准SQL中访问au子数组?

Question

在Bigquery标准SQL中，我尝试只选择"tags.value.name"开始不开箱舱旅行"是真的

但是我不知道怎样才能得到这些东西。

当我尝试这样做时，它不起作用:

SELECT my_table.fl_coins_life_time_balance
FROM my_table
WHERE "start unopen capsule journey" IN UNNEST(my_table.tags.value)

这个也不行

SELECT my_table.fl_coins_life_time_balance
FROM my_table
WHERE "start unopen capsule journey" IN UNNEST(my_table.tags)

你可以在这里查看Table结构

我很抱歉这个问题谁可能是一个新手问题，但非常感谢你的帮助，我有点，但卡在这里^^

Answer 1

这取决于结构中是否有数组以及在何处。如果没有数组，使用this:

With data as (
select struct(struct( "unopen" as name, 8888 as id) as value) tags , 555 fl_coins union all
select struct(struct( "start unopem capsule journey" as name, 8847 as id) as value) tags , 111 fl_coins union all
select struct(struct( "unopen" as name, 8888 as id) as value) tags , 222 fl_coins 
)
SELECT
*
FROM 
data
where tags.value.name="start unopem capsule journey"

如果有数组，使用this:

With data as (
select struct([struct( "unopen" as name, 8888 as id)] as value) tags , 555 fl_coins union all
select struct([struct( "start unopem capsule journey" as name, 8847 as id)] as value) tags , 111 fl_coins union all
select struct([struct( "unopen" as name, 8888 as id)] as value) tags , 222 fl_coins 
)
SELECT
*,tag_
FROM 
data, unnest(data.tags.value) as tag_
where tag_.name="start unopem capsule journey"