No matching signature for function ARRAY_TO_STRING in BigQuery

Sample table Structure:

Fieldname                           Type    Mode
reporteeNames                       RECORD  REPEATED    
reporteeNames.reporteeTitle         STRING  NULLABLE    
reporteeNames.reporteeName          RECORD  NULLABLE    
reporteeNames.reporteeName.display  STRING  NULLABLE    
reporteeNames.reporteeName.value    STRING  NULLABLE

In order to retrieve data from a table with ARRAY fields:

SELECT ARRAY_TO_STRING( reporteeNames, '|') FROM 
`project.Dataset.ExternalNewHire`;

It throws the error: No matching signature for function ARRAY_TO_STRING for 
argument types ARRAY<STRUCT<reporteeTitle STRING...

Solution:

First, UNNEST the array in order to select the field inside the STRUCT:

SELECT
  (SELECT STRING_AGG(reporteeName, '|')
   FROM UNNEST(reporteeNames)) AS names
FROM `project`.Dataset.table

dan-irving

posted on 01 Oct 20