Search code examples
sqlarraysgoogle-bigqueryansi-sql

Condensing Table Array Rows in BigQuery SQL

I want to use BigQuery Standard SQL

I have a table that looks like:

enter image description here

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.

Solution

  • 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;