Condensing Table Array Rows in BigQuery SQL

Question

I want to use BigQuery Standard SQL

I have a table that looks like:

How would I collapse each row? For or instance, so that row #1 looks something like:

row   Canopus_id    facebook     id      wikipedia    freebase musicbrainz    
1      10043474     21258...    Q557     Patt_Smith     /m/05qw5  d1358...

Essentially we are collapsing the row in such a way that it will try to find a filled entry for a column and if there is a filled entry (either in the first or second cell) that will be the value. Else, there will be a null.

is every shown column actually a separate repeated field or it is one repeated record? - would be great to see schema of the table/data — Mikhail Berlyant
– Mikhail Berlyant, Commented Nov 6, 2018 at 22:35
@javacash . . . I'm curious what the types of the columns are. Arrays don't support NULL elements. — Gordon Linoff
– Gordon Linoff, Commented Nov 6, 2018 at 22:55

Gordon Linoff · Accepted Answer · 2018-11-06 22:55:21Z

1

Here is one method:

select row,
       (select max(facebook) from unnest(facebook) facebook) as facebook,
       (select max(id) from unnest(id) id) as id,
       . . .
from t;

answered Nov 6, 2018 at 22:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Condensing Table Array Rows in 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