Bigquery: how to preserve nested data in derived tables?

Question

I have a few large hourly upload tables with RECORD fieldtypes. I want to pull select records out of those tables and put them in daily per-customer tables. The trouble I'm running into is that using QUERY to do this seems to flatten the data out.

Is there some way to preserve the nested RECORDs, or do I need to rethink my approach?

If it helps, I'm using the Python API.

Answer 1

It is now possible to preserve nested field structure in query results.... more here

use flatten_results flag in bq util

--[no]flatten_results: Whether to flatten nested and repeated fields in the result schema. If not set, the default behavior is to flatten.

API Documentation

https://developers.google.com/bigquery/docs/reference/v2/jobs#configuration.query.flattenResults

Answer 2

Unfortunately, there isn't a way to do this right now, since, as you realized, all results are flattened.