CODEWITHSUNDEEP
sqlgoogle-bigqueryunnest
Tim
Tim

Reputation: 263

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
                            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?

Upvotes: 3

Views: 6635

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

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

enter image description here

Upvotes: 5

Related Questions