BigQuery – concatenate array of strings for each row


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
2       id_2    03-16-2011  Pete


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?

>Solution :

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

Leave a ReplyCancel reply