BigQuery - Convert Array of dates into string

Question

I have a table in Big Query, with some fields that are arrays of dates (type Date Repeated). I need to convert them into a string. For arrays of strings fields I am doing it as below, and it works fine:

ARRAY_TO_STRING(national_id, "|", "") AS national_id

But when the field is an array of dates I´m getting below error.

No matching signature for function ARRAY_TO_STRING for argument types: ARRAY, STRING, STRING. Supported signatures: ARRAY_TO_STRING(ARRAY, STRING, [STRING]); ARRAY_TO_STRING(ARRAY, BYTES, [BYTES]) at [41:1]

I also tried casting date to string as below:

ARRAY_TO_STRING(cast(natural_person_date_of_birth_list as string), "|", "") AS natural_person_date_of_birth_list,

But I´m getting below error:

Invalid cast from ARRAY to STRING at [41:22].

Can anyone help me on this issue?

Thank you very much

Answer 1

You can use a subquery in the select list to transform the dates into strings. For example,

SELECT
  ARRAY_TO_STRING(national_id, "|", "") AS national_id,
  (SELECT STRING_AGG(date, "|") FROM UNNEST(natural_person_date_of_birth_list)) AS dates_of_birth
FROM YourTable;

The advantage of this approach is that you get a string for every row, which it sounds like you want.

Answer 2

You can do this using unnest():

select string_agg(cast(dte as string), '|')
FROM (select [date('2017-01-01'), date('2018-01-01')] as aofd
     ) d,
     unnest(aofd) as dte