Is there a way to select nested records into a table?

Question

I have a table, T, in Bigquery which contains simple fields and one nested field.

I would like to effectively "select * from T where ..." and store the result in a new table U.

I want U to have the same schema as T (including the nested field).

Is there a way to do this within Bigquery or is the solution to export/transform/import?

Answer 1

All query results get flattened by default, but we've added a "flatten results" flag to the query that you can set to false if you want nested results. This currently only works when you select a destination table and use "allow large results". If you're using the Web UI, this setting is available by clicking on the "show options" button on the query window. If you're calling the API, you can set "flattenResults=False" in the query job configuration.

Docs for this feature are here.