At first i just want to get the Hostname and cd2 my query look like the following:
SELECT hits.page.hostname, hits.customDimensions.value
FROM `dataset`, UNNEST(hits) as hits
WHERE hits.customDimensions.index = 2
LIMIT 1000
I get the following Error:
Cannot access field index on a value with type ARRAY<STRUCT<index INT64, value STRING>> at [1:162]
So how can i handle two different BigQuery Arrays?
Since you can have up to 200 fields in that array and you usually only want one of them it is better to not cross join with it but write a little subquery.
SELECT
page.hostname,
(SELECT value FROM UNNEST(h.customDimensions) WHERE index=2) AS cd2
FROM `dataset`,
UNNEST(hits) as h
LIMIT 1000
The more data you have the faster this query will perform in comparison to the cross join version. Subquery is always faster than cross join.