Keep getting “Cannot access field item on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, …>>”

Question

I'm copying and pasting the code from the Google Analytics Cookbook:

SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_1’
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_2’
ON Alias_Name_1.hits.item.productSku = Alias_Name_2.hits.item.productSku;

but I edited it to this:

SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ProductSKU_Item
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS product2_item
ON ProductSKU_Item.hits.item.productSku = product2_item.hits.item.productSku;

and I'm still getting an error that says Cannot access field item on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [5:13] but when I try the UNNEST function, it still isn't working and I'm not sure what I'm doing wrong.

Steven Ensslen Steven Ensslen · Accepted Answer · 2020-11-29T21:48:30

The error which I made with unnest was to try to unnest hits.item which is a struct. hits is the array which needs to be unnested. Try this:

SELECT
  hits.item.productSku,
  SUM(hits.hitNumber) / COUNT( fullVisitorId ) AS avg_hit_number
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` t
CROSS JOIN
  UNNEST(t.hits) AS hits
WHERE
  hits.item.productSku IS NOT NULL
  AND totals.transactions >= 1
GROUP BY
  hits.item.productSku

Keep getting “Cannot access field item on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, …>>”

1 Answers