BIgQuery - No matching signature for function ARRAY_TO_STRING

Question

After running below query

           SELECT ARRAY_TO_STRING( reporteeNames, '|') FROM 
         `appsflow-dev.PersonRequisition.ExternalNewHire`;

I am getting error

error

Schema

Fieldname                           Type    Mode

reporteeNames                       RECORD  REPEATED    
reporteeNames.reporteeTitle         STRING  NULLABLE    
reporteeNames.reporteeName          RECORD  NULLABLE    
reporteeNames.reporteeName.display  STRING  NULLABLE    
reporteeNames.reporteeName.value    STRING  NULLABLE

Please advise a solution specific to my schema.

I'm already using Standard SQL. I checked my settings, the SQL Dialect choosen is "Standard". — user9638534
– user9638534, Commented Jan 23, 2019 at 12:58
@AbhishekBapna - can you clarify what exactly result you are expecting! — Mikhail Berlyant
– Mikhail Berlyant, Commented Jan 23, 2019 at 19:15
I was expecting the array to be converted into string to be displayed in a cell for each row. The method given in the answer posted below worked for me. — user9638534
– user9638534, Commented Jan 24, 2019 at 13:22

Elliott Brossard · Accepted Answer · 2019-01-23 12:55:37Z

1

You need to unnest the array in order to select the field inside the struct:

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

See also the working with arrays documentation.

answered Jan 23, 2019 at 12:55

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

BIgQuery - No matching signature for function ARRAY_TO_STRING

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related