BigQuery - concatenate array of strings for each row

Question

Given this input:

Row     id      app_date    inventor   country
1       id_1    01-15-2022  Steve       US
                            Ashley      US
2       id_2    03-16-2011  Pete        US
                            Jamie       US
                            Mary        FR

I need to concatenate inventor strings 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, Jamie, Mary

Following this example, I managed to get here:

Row     id      app_date    inventor
1       id_1    01-15-2022  Steve
                            Ashley
2       id_2    03-16-2011  Pete
                            Jamie
                            Mary

using

WITH sample AS (
  SELECT "id_1" AS id, "01-15-2022" as app_date,
    [STRUCT("Steve" as name, "EN" as country),
     STRUCT("Ashley", "EN")]
       AS inventor
  UNION ALL SELECT "id_2", "03-16-2011",
    [STRUCT("Pete", "EN"), STRUCT("Jamie", "EN"), STRUCT("Mary", "FR")]),

    res as (
        SELECT id, app_date,
            (SELECT ARRAY(SELECT name FROM UNNEST(inventor))) AS inventors
            FROM sample
        )

SELECT id, app_date, inventors
FROM res

that is, the second to last step of that example. Final step seems like ARRAY_TO_STRING on the inventors column, but I'm getting a No matching signature for function ARRAY_TO_STRING for argument types: ARRAY<STRING> error.

What am I missing here?

Mikhail Berlyant · Accepted Answer · 2022-03-15 18:13:02Z

5

Consider below approach

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

if applied to sample data in your question - output is

answered Mar 15, 2022 at 18:13

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

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

1 Comment

Tim Over a year ago

Thank you very much. Is there a way to accomplish this with ARRAY_TO_STRING? I need to handle possible NULLs by providing explicit values.

Collectives™ on Stack Overflow

BigQuery - concatenate array of strings for each row

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related