BigQuery - concatenate array of strings for each row with `null`s

Question

This is a clarification/follow-up on the earlier question where I didn't specify the requirement for null values.

Given this input:

Row     id      app_date    inventor.name   inventor.country
1       id_1    01-15-2022  Steve           US
                            Ashley          US
2       id_2    03-16-2011  Pete            US
                            <null>          US
                            Mary            FR

I need to extract name from inventor struct and concatenate them for each id, like so:

Row     id      app_date    inventors   
1       id_1    01-15-2022  Steve, Ashley
2       id_2    03-16-2011  Pete, ^, Mary

Note custom filling for null value - which, to me, seems like it means I need to use ARRAY_TO_STRING specifically that supports this.

The closest example I found doesn't work with nulls. How can one do this?

Mikhail Berlyant · Accepted Answer · 2022-03-15 19:43:34Z

1

Use below

SELECT * EXCEPT(inventor), 
  (SELECT STRING_AGG(IFNULL(name, '^'), ', ') FROM t.inventor) inventors
FROM sample t

with output

answered Mar 15, 2022 at 19:43

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

BigQuery - concatenate array of strings for each row with `null`s

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related