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`;
\n\n
It throws the error: No matching signature for function ARRAY_TO_STRING for \nargument types ARRAY<STRUCT<reporteeTitle STRING...
\n\n

Solution:

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

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

dan-irving

posted on

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds