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

Question

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:

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?

Mikhail Berlyant · Accepted Answer · 2021-11-15 18:14:05Z

1

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

answered Nov 15, 2021 at 18:14

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ricco D · Accepted Answer · 2021-11-16 03:17:56Z

1

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.

See query below:

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

Result:

edited Nov 16, 2021 at 3:17

answered Nov 16, 2021 at 3:07

Ricco D

7,3451 gold badge11 silver badges19 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related