BigQuery: error with standard-sql for querying a table?

Question

Can you help me please with this converting this query in standard sql for BigQuery? I got this error Cannot access field page on a value with type ARRAY> at hits.page.hostname LIKE 'website.fr'. Please tell me to deal with that issue to correct it.

#standardSQL
SELECT DATE,MAX(CASE
  WHEN customDimensions.index = 1 THEN customDimensions.value END) AS CUSTOMDIMENSIONS_VALUE,  visitNumber,  fullvisitorid,  visitStartTime,  SEC_TO_TIMESTAMP(visitStartTime) AS humain,  TIME (visitStartTime+3600 ) AS Paris_timezone,  hits.hour,  hits.minute,  CONCAT(fullvisitorid, STRING(visitid)) AS sessionid,  MAX(CASE
  WHEN hits.customDimensions.index = 11 THEN hits.customDimensions.value END) AS localproductname,
device.deviceCategory,
hits.page.pagePath,
IFNULL(hits.page.pagePathLevel2,'') AS HITS_PAGE_PAGEPATHLEVEL2,
IFNULL(hits.page.pagePathLevel3,'') AS HITS_PAGE_PAGEPATHLEVEL3,
MAX(CASE WHEN hits.customDimensions.index = 14 THEN hits.customDimensions.value END) AS assetpurpose,
MAX(CASE WHEN hits.customDimensions.index = 27 THEN hits.customDimensions.value END) AS pathology,
hits.hitNumber
FROM  `85801771.ga_sessions*`
CROSS JOIN UNNEST(customDimensions) AS customDimension
CROSS JOIN UNNEST(hits) AS hit
WHERE _TABLE_SUFFIX BETWEEN '20161025' AND '20161026'
AND hits.page.hostname LIKE 'website.fr'
AND customDimensions.value != "null"
  AND hits.page.pagePath LIKE 'poiu'
  AND hits.type = 'PAGE'
  AND (customDimensions.index = 1
  OR hits.customDimensions.index = 11
  OR hits.customDimensions.index = 14
  OR hits.customDimensions.index = 27 )
GROUP BY 
DATE,
visitStartTime,
humain,
Paris_timezone,
hits.hour,
hits.minute,
fullVisitorId,
sessionid,
visitNumber,
device.deviceCategory,
hits.page.pagePath,
HITS_PAGE_PAGEPATHLEVEL2,
HITS_PAGE_PAGEPATHLEVEL3,
hits.hitNumber
ORDER BY DATE,
visitStartTime,
device.deviceCategory,
humain,
Paris_timezone,
hits.hour,
hits.minute,
fullVisitorId,
sessionid,
visitNumber,
hits.page.pagePath,
HITS_PAGE_PAGEPATHLEVEL2,
HITS_PAGE_PAGEPATHLEVEL3,
hits.hitNumber DESC
LIMIT 100000

Answer 1

In your query, you give the alias hit to the elements returned by UNNEST(hits), and the alias customDimension to the elements returned by UNNEST(customDimensions). If you want to refer to a particular hit rather than the array, use hit instead, and if you want to refer to a single custom dimension, use customDimension instead. For example, your filter might be something like:

WHERE _TABLE_SUFFIX BETWEEN '20161025' AND '20161026' AND
  hit.page.hostname LIKE '%website.fr%' AND
  customDimension.value != "null" AND
  hit.page.pagePath LIKE '%poiu%' AND
  hit.type = 'PAGE' AND
  customDimension.index IN (1, 11, 14, 27)

Answer 2

There are couple of mistakes that i found it in the query. I made the corrections, hope this would help you.

SELECT DATE,MAX(CASE
  WHEN customDimensions.index = 1 THEN customDimensions.value END) AS CUSTOMDIMENSIONS_VALUE,  visitNumber,  fullvisitorid,  visitStartTime, TIMESTAMP_SECONDS(visitStartTime) AS humain, EXTRACT(DATE FROM TIMESTAMP_SECONDS(visitStartTime) AT TIME ZONE 'Paris_timezone')  AS Paris_timezone, 
  hits.hour,  hits.minute,  CONCAT(CAST(fullVisitorId AS string),CAST(visitId AS string)) AS sessionid,  MAX(CASE
  WHEN customDimensions.index = 11 THEN customDimensions.value END) AS localproductname,
device.deviceCategory,
hits.page.pagePath,
IFNULL(hits.page.pagePathLevel2,'') AS HITS_PAGE_PAGEPATHLEVEL2,
IFNULL(hits.page.pagePathLevel3,'') AS HITS_PAGE_PAGEPATHLEVEL3,
MAX(CASE WHEN customDimensions.index = 14 THEN customDimensions.value END) AS assetpurpose,
MAX(CASE WHEN customDimensions.index = 27 THEN customDimensions.value END) AS pathology,
hits.hitNumber
FROM `85801771.ga_sessions*`
CROSS JOIN UNNEST(customDimensions) AS customDimensions
CROSS JOIN UNNEST(hits) AS hits
WHERE _TABLE_SUFFIX BETWEEN '20161025' AND '20161026'
AND hits.page.hostname LIKE 'website.fr'
AND customDimensions.value != "null"
  AND hits.page.pagePath LIKE 'poiu'
  AND hits.type = 'PAGE'
  AND (customDimensions.index = 1
  OR customDimensions.index = 11
  OR customDimensions.index = 14
  OR customDimensions.index = 27 )
GROUP BY 
DATE,
visitStartTime,
humain,
Paris_timezone,
hits.hour,
hits.minute,
fullVisitorId,
sessionid,
visitNumber,
device.deviceCategory,
hits.page.pagePath,
HITS_PAGE_PAGEPATHLEVEL2,
HITS_PAGE_PAGEPATHLEVEL3,
hits.hitNumber
ORDER BY DATE,
visitStartTime,
device.deviceCategory,
humain,
Paris_timezone,
hits.hour,
hits.minute,
fullVisitorId,
sessionid,
visitNumber,
hits.page.pagePath,
HITS_PAGE_PAGEPATHLEVEL2,
HITS_PAGE_PAGEPATHLEVEL3,
hits.hitNumber DESC
LIMIT 100000