CODEWITHSUNDEEP
google-bigquery
cdudek
cdudek

Reputation: 133

Google BigQuery: UNNEST array of structs and unnested item as struct

I have a question regarding UNNESTing 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

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

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

Related Questions