Reputation: 133
I have a question regarding UNNEST
ing an Array-column with structs.
My source table schema looks like this
fields:
- id: STRING::NULLABLE
- response_choices: RECORD::REPEATED
- response_choices.response_option_id: STRING::NULLABLE
- response_choices.position: INT64::NULLABLE
- response_choices.rendered_position: INT64::NULLABLE
- response_choices.responded_at: DATETIME::NULLABLE
When I executing the following query
SELECT
* EXCEPT(response_choices),
STRUCT(
ro.response_option_id AS response_option_id,
ro.position AS position,
ro.rendered_position AS rendered_position,
ro.response_datetime AS responded_at
) AS response_choice
FROM my_responses_table,
UNNEST(response_choices) AS ro
The query returns data that includes both the struct (as defined above) but it also adds the columns for the struct flat to the results. So the schema looks like the following
fields:
- id: STRING::NULLABLE
- response_option_id: STRING::NULLABLE
- position: INT64::NULLABLE
- rendered_position: INT64::NULLABLE
- responded_at: DATETIME::NULLABLE
- response_choice: RECORD::NULLABLE
- response_choice.response_option_id: STRING::NULLABLE
- response_choice.position: INT64::NULLABLE
- response_choice.rendered_position: INT64::NULLABLE
- response_choice.responded_at: DATETIME::NULLABLE
However I'd UNNEST the array in a way that it only adds one field with the struct in it. The reason is that some of the fields in struct conflict with fields that are already in the table source.
I’d like to get something like the following
fields:
- id: STRING::NULLABLE
- response_choice: RECORD::NULLABLE
- response_choice.response_option_id: STRING::NULLABLE
- response_choice.position: INT64::NULLABLE
- response_choice.rendered_position: INT64::NULLABLE
- response_choice.responded_at: DATETIME::NULLABLE
Any suggestions or pointers in the right direction would be much appreciated! Thank you
Upvotes: 2
Views: 4365
Reputation: 173210
Below is for BigQuery Standard SQL
#standardSQL
select t.* except(response_choices),
response_choice
from `project.dataset.my_responses_table` t,
unnest(response_choices) response_choice
Upvotes: 3