BigQuery - concatenate array of strings for each row

Tim Published at Dev

Tim

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

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2022-03-16

Comments

0 comments

Concatenate strings from an object in th:each with Thymeleaf

How to concatenate 2 strings in a multi dimensional array?

C# Concatenate strings or array of chars

BigQuery - Concatenate multiple rows into a single row

efficiently concatenate array of strings

Python: Pandas Concatenate each row into a string

Bigquery array of STRINGs to array of INTs

Concatenate Strings using some array elements (Javascript)

Concatenate Strings In For Each Loop

Putting splitted strings into each row

How to concatenate multiple rows of strings into one row?

Concatenate strings in BigQuery

Converting each row into a dataframe and concatenate results

concatenate vector of strings into a single string - for each row in df

PostgreSQL concatenate two tables on each row

How to concatenate certain strings to every element of array

Trying to concatenate a series of strings via array

Concatenate strings in foreach loop with array key

Ruby:concatenate pairs of strings in an array

Concatenate a string to each object of an array

numpy array concatenate with extra column to each array

concatenate each characters from 2 strings in a list

How to concatenate all strings of an array in Minizinc?

BigQuery - Concatenate multiple rows and columns into a single row

bigquery transpose and concatenate for each record

Concatenate a numpy array of strings to numpy array of numbers

Concatenate each object property values of javascript array , properties are list of strings in javascript

Group row data in a 2d array by the first 3 columns then concatenate the remaining columns in each group with commas

Pandas concatenate strings and numpy array

TOP Ranking

Article

BigQuery - concatenate array of strings for each row

BigQuery - concatenate array of strings for each row

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

How to import an asset in swift using Bundle.main.path() in a react-native native module

pump.io port in URL

Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

BigQuery - concatenate ignoring NULL

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

How to remove the extra space from right in a webview?

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

flutter: dropdown item programmatically unselect problem

How to use merge windows unallocated space into Ubuntu using GParted?

Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

Nuget add packages gives access denied errors

Svchost high CPU from Microsoft.BingWeather app errors

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

12.04.3--- Dconf Editor won't show com>canonical>unity option

Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

Any way to remove trailing whitespace FOR EDITED lines in Eclipse [for Java]?