CODEWITHSUNDEEP
google-bigquery
Canovice
Canovice

Reputation: 10163

BigQuery unnest inner string column from ARRAY<STRUCT<STRING, STRING>>

I am having trouble creating a reproducible example here, because I'm not quite sure how to create a demo example table with a column of type ARRAY<STRUCT<year STRING, statCrewShirtNumber STRING>>. We have the following table:

enter image description here

And we are simply trying to turn year into its own column. We try the obvious:

select 
    customValues.year as year
from dataset.our_table

and get the error Cannot access field year on a value with type ARRAY<STRUCT<year STRING, statCrewShirtNumber STRING>>. So this is not a basic struct, but a struct inside of an array. How can we create a separate column for year?

Upvotes: 0

Views: 1845

Answers (2)

Ricco D
Ricco D

Reputation: 7277

Using this sample table, I also have a struct in an array as well. You can simply use UNNEST to have separate columns for each field.

enter image description here

See query below:

SELECT gender FROM `sandbox.test_table`, UNNEST(bikerides)

Result:

enter image description here

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

You need first to unnest the array as in example below

select customValue.year as year
from `dataset.our_table` t, 
t.customValues as customValue

Upvotes: 1

Related Questions