Using ARRAY_TO_STRING with integer array

Question

ARRAY_TO_STRING() works with string arrays but is not supported with integer arrays.

I have a repeated column that contains a list of IDs, I want to export this from a US dataset to an EU dataset. So far I'm using

bq query --nouse_legacy_sql --allow_large_results --max_rows=100000000000 --format=csv < {sql_file} > {output_file} but this does not work with nested columns as CSV does not support them.

Another option is exporting as json but I would need to convert this to newline JSON. I could use jq to convert this but it is not installed on the server by default.

Instead of that, I'm going down the path of flattening repeated columns before export. What I think I need to do is convert every element in the array to string and then use ARRAY_TO_STRING(). Am I going down the right path here?

I believe I want something similar to

SELECT ARRAY_TO_STRING(ARRAY((SELECT CAST(* AS STRING)), ';') FROM UNNEST(segments)) FROM my_table

but this gives me Syntax error: Unexpected "*" as it should. Any ideas?

Mikhail Berlyant · Accepted Answer · 2020-03-24 20:32:21Z

10

Below is for BigQuery Standard SQL

#standardSQL
SELECT (
    SELECT STRING_AGG(CAST(id AS STRING), ';') 
    FROM UNNEST(segments) id
  ) 
FROM `project.dataset.mytable`

answered Mar 24, 2020 at 20:32

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

4 Comments

Josh Laird Over a year ago

Ah, aliasing the unnest was the missing piece. Thank you!

Mikhail Berlyant Over a year ago

right. consider voting up and accept the answer if it helped :o)

Mikhail Berlyant Over a year ago

btw, in addition to aliasing - as you can see in my answer - you can directly generate needed string avoiding first creating array and then transform it to string

Josh Laird Over a year ago

Indeed, very nice 🔥

Lukas Eder · Accepted Answer · 2025-11-25 16:14:20Z

0

This answer doesn't necessarily produce array elements in the right order. Here's a version that aggregates elements in the order of appearance within the array:

SELECT (
  SELECT string_agg(cast(v AS string), ';' ORDER BY o)
  FROM unnest(a) AS v WITH OFFSET AS o
)
FROM (SELECT ARRAY[1,2,3] AS a) AS t

edited 22 hours ago

answered 22 hours ago

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Collectives™ on Stack Overflow

Using ARRAY_TO_STRING with integer array

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related