Google Big Query gives Cannot Access Field page on a value with type ARRAY

Question

I am working on getting the qulifiedVisitor count. the qulifiedVisitor will be the user who searched 'apple' on the website.

but I am getting this Error:

Cannot access filed page on a value with type ARRAY<STRUCT<hitNumber INT64,timeITN64, time64,..>>

I tried the method that other people provided before, such as using UNNEST(hits), or exist(). However, it still not working.

Please let me know if you have any thoughts.

Screenshot of the code and error.

Answer 1

I think you want something like this:

SELECT
  COUNT(DISTINCT fullVisitorId) AS QualifiedVisitors
FROM `dataset.tablename`, UNNEST(hits) AS hits
WHERE hits.page.pagePath = '/apple';

The important part is aliasing the result of UNNEST. In the screenshot that you shared, you are missing an alias, so hits.page.pagePath still refers to the original hits column rather than the unnested one.

Edit: If the intention is to compute totals where at least one of the entries in hits has a pagePath of '/apple', you may want to use an EXISTS clause rather than flattening the array at the outer scope. For example,

SELECT
  COUNT(DISTINCT fullVisitorId) AS QualifiedVisitors,
  SUM(totals.pageViews) AS TotalViews
FROM `dataset.tablename`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(hits) AS hit
  WHERE hit.page.pagePath = '/apple');