CODEWITHSUNDEEP
google-bigquery
Lior
Lior

Reputation: 1457

BigQuery - joining on a repeated field

I'm trying to run a join on a repeated field.

Originally I get an error:

Cannot join on repeated field payload.pages.action

I fix this by running flatten on the relevant table (this is only an example query - it will give empty result if it would successfully run):

SELECT 
  t1.repository.forks
FROM publicdata:samples.github_nested t1
left join each flatten(publicdata:samples.github_nested,payload.pages) t2 
on t2.payload.pages.action=t1.repository.url

I get a different error:

Table wildcard function 'FLATTEN' can only appear in FROM clauses

This used to work in the past. Is there some syntax change?

Upvotes: 3

Views: 4341

Answers (1)

Jordan Tigani
Jordan Tigani

Reputation: 26617

I don't think there has been a syntax change, but you should be able to wrap the flatten statement in a subselect. That is,

SELECT 
  t1.repository.forks
FROM publicdata:samples.github_nested t1
left join each (SELECT * FROM flatten(publicdata:samples.github_nested,payload.pages)) t2 
on t2.payload.pages.action=t1.repository.url

Upvotes: 2

Related Questions