Bigquery "Not a Leaf Field" Error

Question

This query:

SELECT payload.pages FROM FLATTEN([publicdata:samples.github_nested] , payload)
ORDER BY created_at
LIMIT 1000

Results in this error:

Error: Field payload.pages from table publicdata:samples.github_nested is not a leaf field.

Is this a bug or or am I doing something wrong?

Answer 1

See https://developers.google.com/bigquery/query-reference#flatten

First, you need to give FLATTEN a repeated field to flatten. payload isn't repeated. payload.pages is. Second, payload.pages, in your select statement, isn't a leaf field, it is a nested / repeated one. So you switch it to use payload.pages.*

So your query would become:

SELECT payload.pages.* 
FROM FLATTEN([publicdata:samples.github_nested] , payload.pages)
ORDER BY created_at
LIMIT 1000