Unable to un-nest some fields using google bigquery (standard)

Question

I have a nested table that I can not access all fields of using standard google 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]"

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.

what you mean by "unable to un-nesting ..."? you haven't even tried! at least that is how it looks from queries in your question! — Mikhail Berlyant, Oct 19 '17 at 19:15
Hello and welcome to Stackoverflow! If the answers you received helped you in any way or solved your issue then consider accepting and up-voting as this is important in this forum: https://stackoverflow.com/help/someone-answers — Willian Fuks, Oct 20 '17 at 14:06

score 1 · Answer 1 · answered Oct 19 '17 at 19:12

1

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,

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'

answered Oct 19 '17 at 19:12

Elliott Brossard

32,095
2
67
99

Thanks that does work! However I am confused as to why it is necessary for SKU and not rev or currency. In particular, I don't get why the auto flatten that standard is apparently doing does not do this directly. – user2998362 Oct 19 '17 at 19:54
It's only necessary for `sku` because the field containing it (`items`) is an array. For the other field paths such as `currency` and `amount`, there is no array along the path. When using standard SQL, there is no "auto flattening"; you have to express your intentions explicitly (like with the comma operator in this case). – Elliott Brossard Oct 19 '17 at 20:02

Mikhail Berlyant · Accepted Answer · 2017-10-19T21:07:20.297

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

I feel below is what you might need - it is for 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

Added, as per Elliott's comment/suggestion

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

Or maybe `ARRAY(SELECT sku FROM UNNEST(t.rev_info.purchase.virtual_items.items))` AS sku to avoid the aggregation (you could use `STRING_AGG` alternatively). — Elliott Brossard, Oct 19 '17 at 20:29
Totally agree. If it were my code - i would most likely use something like `(SELECT STRING_AGG(item.sku) FROM UNNEST(...) item) AS SKUs` without `GROUP BY` and without `SELECT *`, etc. But what I've learnt here on SO answering each and every day during last two years is that usually OP try to "simplify"/obfuscate their code in many cases leaving outside "small" from their prospective but really important pieces that matter, but usually they do not transform/change the structure of query. SO in this case - `SELECT *` looked a little suspicious to me so I tried to not to change inner query — Mikhail Berlyant, Oct 19 '17 at 21:04

Unable to un-nest some fields using google bigquery (standard)

2 Answers2