10

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?

Eric Kamm
  • 101
  • 1
  • 6

1 Answers1

9

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.

Jordan Tigani
  • 26,089
  • 4
  • 60
  • 63
  • Consider this another vote for that feature! It would really help as a post-processing step to be able to slice & dice imported data into different tables according to different retention policies, etc. Also for performing simple transformations on imported data with nested records. – cce Oct 30 '13 at 16:17
  • I think you must have put your comment above on the wrong thread. But still would love to see a "don't flatten the results" query option for SELECT into destination tables! Thanks! – cce Nov 22 '13 at 19:14
  • You're right, I did add the comment to the wrong question. Generally when there are feature requests in a question if you upvote them we can use that as a measure of support for the answer (upvote the question not the answer, this isn't a ploy to get karma points :-) ) – Jordan Tigani Nov 22 '13 at 19:27
  • FYI the option to avoid flattening results is now available; I've updated my answer above. – Jordan Tigani Aug 29 '14 at 14:48
  • Is there a reason behind the need of the `allowLargeResults` when using `flattenResults=false` ? Using `allowLargeResults` can increase the query time and forces you to define a destination table, which is pretty inconvenient in some cases. – Gilberto Torrezan Jun 22 '16 at 19:28
  • Yes there is a reason, no it isn't a good reason. The reason is related to the monstrously complex code that handles query results. The good news is this is fixed in standard sql, which never flattens results. https://cloud.google.com/bigquery/sql-reference/ – Jordan Tigani Jun 23 '16 at 15:45