BiqQuery Error: Cannot join on repeated field 'customDimensions.value'

Question

I have the following SQL statement, in short, table contains a list of ids that I need to get the website behavior for the past year. These ids should match customDimensions.value

SELECT fullVisitorId, visitNumber, totals.pageviews, customDimensions.value as client_id
FROM (
SELECT * FROM
  TABLE_DATE_RANGE([xxxxxxx.ga_sessions_],
                    TIMESTAMP('2017-01-01'),
                    TIMESTAMP('2018-01-01'))
                    )
WHERE customDimensions.index = 2 AND customDimensions.value IN (SELECT STRING(id) FROM [table])
GROUP BY 1,2,3,4

I get the error:

Error: Cannot join on repeated field customDimensions.value

Answer 1

I see you are still with BigQuery Legacy SQL so below is for legacy sql

Assuming that the rest of your original query does what you want - it addresses below error

Error: Cannot join on repeated field customDimensions.value

#legacySQL
SELECT fullVisitorId, visitNumber, totals.pageviews, customDimensions.value AS client_id
FROM FLATTEN((
SELECT * FROM
  TABLE_DATE_RANGE([xxxxxxx.ga_sessions_],
                    TIMESTAMP('2017-01-01'),
                    TIMESTAMP('2018-01-01'))
                    ), customDimensions)
WHERE customDimensions.index = 2 AND customDimensions.value IN (SELECT STRING(id) FROM [table])
GROUP BY 1,2,3,4