我有一个嵌套表,我无法使用标准谷歌大查询访问所有字段。
例如,此查询失败
SELECT *
FROM
(
SELECT
rev_info.user.id as player_id,
rev_info.purchase.total.currency as currency,
rev_info.purchase.total.amount as REV
,rev_info.purchase.virtual_items.items.sku as sku
FROM `gcs.rev`
)
WHERE currency = 'USD'
有错误
"错误:无法访问 ARRAY 类型的值的字段 SKU>在 [9:59]"
然而
SELECT *
FROM
(
SELECT
rev_info.user.id as player_id,
rev_info.purchase.total.currency as currency,
rev_info.purchase.total.amount as REV
--,rev_info.purchase.virtual_items.items.sku as sku
FROM `gcs.rev`
)
WHERE currency = 'USD'
此查询很好。
另请注意
SELECT
rev_info.purchase.virtual_items.items.sku as sku
FROM `gcs.rev`
失败,出现与上述相同的错误。
扩展 Elliott 的答案 - 我认为在这里您首先需要取消嵌套,但随后您很可能需要聚合回您的sku
。 否则你会得到相当多余(扁平化)的输出
我觉得下面是您可能需要的 - 它适用于 BigQuery Standard SQL
#standardSQL
SELECT
player_id,
currency,
REV,
STRING_AGG(sku) SKUs
FROM (
SELECT
rev_info.user.id AS player_id,
rev_info.purchase.total.currency AS currency,
rev_info.purchase.total.amount AS REV,
item.sku AS sku
FROM `gcs.rev` t,
UNNEST(t.rev_info.purchase.virtual_items.items) item
)
WHERE currency = 'USD'
GROUP BY 1, 2, 3
因此,所有 SKU 都将显示为给定player_id的列表,以及金额和货币
根据艾略特的评论/建议添加
#standardSQL
SELECT
rev_info.user.id AS player_id,
rev_info.purchase.total.currency AS currency,
rev_info.purchase.total.amount AS REV,
(SELECT STRING_AGG(item.sku)
FROM UNNEST(t.rev_info.purchase.virtual_items.items) item
) AS SKUs
FROM `gcs.rev` t,
WHERE currency = 'USD'
如果您的目标是为每个items
数组元素获取一行,则可以在表和rev_info.purchase.virtual_items.items
之间使用逗号(join)运算符。例如
SELECT *
FROM (
SELECT
rev_info.user.id as player_id,
rev_info.purchase.total.currency as currency,
rev_info.purchase.total.amount as REV,
item.sku as sku
FROM `gcs.rev` t,
t.rev_info.purchase.virtual_items.items item
)
WHERE currency = 'USD'