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 \n`project.Dataset.ExternalNewHire`;

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

Solution:

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

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

dan-irving

posted on