Невозможно отменить вложение некоторых полей с помощью google bigquery (стандартно)

У меня есть вложенная таблица, к которой я не могу получить доступ ко всем полям с помощью стандартного google bigquery.

Например, этот запрос не работает

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'

с ошибкой

«Ошибка: невозможно получить доступ к полю sku для значения с типом ARRAY> в [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`

не работает с той же ошибкой, что и выше.


r google-bigquery unnest
person user2998362    schedule 19.10.2017    source источник
comment
что вы подразумеваете под невозможностью распаковки ...? ты даже не пробовал! по крайней мере, так это выглядит из запросов в вашем вопросе!   -  person Mikhail Berlyant    schedule 19.10.2017
comment
Привет и добро пожаловать в Stackoverflow! Если ответы, которые вы получили, помогли вам каким-либо образом или решили вашу проблему, рассмотрите возможность принятия и голосования, поскольку это важно на этом форуме: stackoverflow .com/help/someone-answers   -  person Willian Fuks    schedule 20.10.2017

Ответы (2)


arrow_upward
1
arrow_downward

Расширяя ответ Эллиотта - я думаю, что здесь вам сначала нужно UNNEST, но затем вам, скорее всего, нужно собрать обратно свои sku. в противном случае вы получите довольно избыточный (сплющенный) вывод

Я чувствую, что ниже вам может понадобиться - это для стандартного SQL BigQuery

#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

Таким образом, все артикулы будут представлены в виде списка для данного 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'
person Mikhail Berlyant    schedule 19.10.2017
comment
Или, может быть, ARRAY(SELECT sku FROM UNNEST(t.rev_info.purchase.virtual_items.items)) AS sku, чтобы избежать агрегации (в качестве альтернативы вы можете использовать STRING_AGG). - person Elliott Brossard; 19.10.2017
comment
Полностью согласен. Если бы это был мой код, я бы, скорее всего, использовал что-то вроде (SELECT STRING_AGG(item.sku) FROM UNNEST(...) item) AS SKUs без GROUP BY и без SELECT * и т. д. Но то, что я узнал здесь, отвечая каждый день в течение последних двух лет, так это то, что обычно ОП пытаются упростить/запутать свои код во многих случаях оставляя за бортом небольшие от их предполагаемых, но действительно важные куски, которые имеют значение, но обычно они не трансформируют/изменяют структуру запроса. ТАК в этом случае - SELECT * выглядело для меня немного подозрительно, поэтому я попытался не менять внутренний запрос - person Mikhail Berlyant; 20.10.2017

arrow_upward
1
arrow_downward

Если ваша цель — получить одну строку для каждого элемента массива items, вы можете использовать оператор запятой (соединение) между таблицей и rev_info.purchase.virtual_items.items. Например,

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'
person Elliott Brossard    schedule 19.10.2017
comment
Спасибо, это работает! Однако я не понимаю, почему это необходимо для SKU, а не для оборота или валюты. В частности, я не понимаю, почему автоматическое сглаживание этого стандарта, по-видимому, не делает этого напрямую. - person user2998362; 19.10.2017
comment
Это необходимо только для sku, потому что поле, содержащее его (items), является массивом. Для других путей к полю, таких как currency и amount, вдоль пути нет массива. При использовании стандартного SQL автоматическое выравнивание не выполняется; вы должны явно выразить свои намерения (например, с оператором запятой в этом случае). - person Elliott Brossard; 19.10.2017