CODEWITHSUNDEEP
google-bigquery
CuriousMind
CuriousMind

Reputation: 34135

Bigquery return nested results without flattening it without using a table

It is possible to return nested results(RECORD type) if noflatten_results flag is specified but it is possible to just view them on screen without writing it to table first.

for example, here is an simple user table(my actual table is big large(400+col with multi-level of nesting)

ID,
name: {first, last}

I want to view record particular user & display in my applicable, so my query is

SELECT * FROM dataset.user WHERE id=423421 limit 1

is it possible to return the result directly?

Upvotes: 1

Views: 546

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

You should write your output to "temp" table with noflatten_results option (also respective expiration to be set to purge table after it is used) and serve your client out of this temp table. All "on-fly"
Have in mind that no matter how small "temp" table is - if you will be querying it (in above second step) you will be billed for at least 10MB, so you better use Tabledata.list API in this step (https://cloud.google.com/bigquery/docs/reference/v2/tabledata/list) which is free!

Upvotes: 2

Pentium10
Pentium10

Reputation: 207838

So if you try to get repeated records it will fail on the interface/BQ console with the error:

Error: Cannot output multiple independently repeated fields at the same time.

and in order to get past this error is to FLATTEN your output.

Upvotes: 0

Related Questions