Combine several fields in array of structs BigQuery SQL

Question

I currently have a table with a column C where entry for some row R in column C contains an array of structs S = {a: 1, b:2, c: 3}. I want to combine all the "a" values in this table and retain all the distinct "a" values.

That is to say, for R: 1 and C: 1, I have some array A = [S(a: 1, b: 2, c: 3), S(a: 3, b: 4, c: 5)] --> new table that looks like

a
1
3

How would I do this? Thank you.

Edit: Adding a sample of what the original table looks like:

Row	Data
1	Array<Struct(a: 1, b: 2, c: 3), Struct(a: 3, b: 4, c: 5)>
2	Array<Struct(a: 5, b: 3, c: 3), Struct(a: 6, b: 8, c: 9)>

Desired Output:

a
1
3
5
6

it is not clear what your original data/table looks like - please provide better sample! — Mikhail Berlyant
– Mikhail Berlyant, Commented Oct 13, 2021 at 18:35
what the data type of Column Data? is it STRING as it looks in your sample or REPEATED RECORD or something else? I think the best way for you to show us a schema of table! — Mikhail Berlyant
– Mikhail Berlyant, Commented Oct 13, 2021 at 18:42

Mikhail Berlyant · Accepted Answer · 2021-10-13 18:56:58Z

1

Consider below simple approach

select record.a
from your_table, unnest(data) record

if applied to sample data in your question - output is

answered Oct 13, 2021 at 18:56

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

Collectives™ on Stack Overflow

Combine several fields in array of structs BigQuery SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related