Is it possible to concatenate using STRING_AGG on an array that was created with SPLIT() IN BigQuery

Question

I am trying to use string_agg to concatenate an array that was created using SPLIT().

The code for the array I'm trying to concatenate looks like this:

ARRAY (
   SELECT AS STRUCT
     SPLIT(CustomField.SubComponents, ' | ') AS Name
 ) AS SubComponents,

I have unnested Supcomponents in my Joins using LEFT JOIN UNNEST (Subcomponents) AS Subcomponents

However, I am getting the following error: No matching signature for aggregate function STRING_AGG for argument types: ARRAY <STRING>

Answer 1

I just figured this out, I had to UNNEST twice, once as LEFT JOIN UNNEST (Subcomponents) AS Subcomponents

AND then again as LEFT JOIN UNNEST (SubComponents.Name) AS SubComponentsName

This allowed me to use string_agg succesfully.