Bigquery array of STRINGs to array of INTs

Question

I'm trying to pull an array of INT64 s in BigQuery standard SQL from a column which is a long string of numbers separated by commas (for example, 2013,1625,1297,7634). I can pull an array of strings easily with:

SELECT
  SPLIT(string_col,",")
FROM
  table

However, I want to return an array of INT64 s, not an array of strings. How can I do that? I've tried

CAST(SPLIT(string_col,",") AS ARRAY<INT64>)

but that doesn't work.

Mikhail Berlyant · Accepted Answer · 2017-07-12 23:19:59Z

10

Below is for BigQuery Standard SQL

#standardSQL
WITH yourTable AS (
  SELECT 1 AS id, '2013,1625,1297,7634' AS string_col UNION ALL
  SELECT 2, '1,2,3,4,5'
)
SELECT id, 
  (SELECT ARRAY_AGG(CAST(num AS INT64)) 
    FROM UNNEST(SPLIT(string_col)) AS num
  ) AS num,
  ARRAY(SELECT CAST(num AS INT64) 
    FROM UNNEST(SPLIT(string_col)) AS num
  ) AS num_2
FROM yourTable

edited Jul 12, 2017 at 23:19

answered Jul 12, 2017 at 23:02

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

1 Comment

Elliott Brossard Over a year ago

You beat me to it :) Maybe demonstrate using an ARRAY subquery too? (That's what I was going to post).

Victor Mota · Accepted Answer · 2017-07-13 00:01:25Z

0

Mikhail beat me to it and his answer is more extensive but adding this as a more minimal repro:

SELECT CAST(num as INT64) from unnest(SPLIT("2013,1625,1297,7634",",")) as num;

answered Jul 13, 2017 at 0:01

Victor Mota

1,26912 silver badges17 bronze badges

1 Comment

Mikhail Berlyant Over a year ago

Check this : "However, I want to return an array of INT64s" :o)

Collectives™ on Stack Overflow

Bigquery array of STRINGs to array of INTs

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related