无法使用google bigquery（标准）取消嵌套某些字段

I have a nested table that I can not access all fields of using standard google bigquery.

我有一个嵌套的表,我无法访问使用标准谷歌bigquery的所有领域。

For example this query fails

例如,此查询失败

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'

with error

"Error: Cannot access field sku on a value with type ARRAY> at [9:59]"

“错误:无法在[9:59]*问类型为ARRAY的值的字段sku”

however

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'

This query is fine.

这个查询很好。

Also note that

另请注意

SELECT
       rev_info.purchase.virtual_items.items.sku     as sku 
FROM `gcs.rev`

fails with the same error as above.

失败并出现与上述相同的错误。

2 个解决方案

#1

Expanding on Elliott's answer - I think here you First need to UNNEST, but then you most likely need to aggregate back your sku's. otherwise you will get quite redundant (flattened) output

扩展Elliott的答案 - 我想在这里你首先需要UNNEST,但是你很可能需要聚合你的sku's。否则你会得到相当多余(扁平化)的输出

I feel below is what you might need - it is for BigQuery Standard SQL

我觉得下面是你可能需要的东西 - 它适用于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

So, all sku will be presented as a list for given player_id, along with amount and currency

因此,所有sku都将显示为给定player_id的列表,以及金额和货币

Added, as per Elliott's comment/suggestion

根据Elliott的评论/建议添加

#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'

#2

If your goal is to get one row for every items array element, then you can use the comma (join) operator between the table and rev_info.purchase.virtual_items.items. For example,

如果您的目标是为每个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'

#1