01 Oct 20 · dan-irving · #bigquery ·   Bookmark   ×

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

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