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

have you considered moving to bigquery standard sql? that would really simplify your life especially in such cases — Mikhail Berlyant, Apr 17 '18 at 20:01
@MikhailBerlyant Apologies, I've read the answer, but it was early morning and I moved along. I try to consistently mark/upvote all answers and the help is greatly appreciated! — GRS, Apr 23 '18 at 23:17

score 1 · Accepted Answer · answered Apr 17 '18 at 20:12

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

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

1 Answers1