How to convert stringified array into array in BigQuery?

Question

It so happens I have a stringified array in a field in BigQuery

'["a","b","c"]'

and I want to convert it to an array that BigQuery understands. I want to be able to do this in standard SQL:

with k as (select '["a","b","c"]' as x)
select unnested_x from k, unnest(x) unnested_x

I have tried JSON_EXTRACT('["a","b","c"]','$') and everything else I could find online.

Any ideas?

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
WITH k AS (
  SELECT 1 AS id, '["a","b","c"]' AS x UNION ALL
  SELECT 2, '["x","y"]' 
)
SELECT 
  id, 
  ARRAY(SELECT * FROM UNNEST(SPLIT(SUBSTR(x, 2 , LENGTH(x) - 2)))) AS x
FROM k

It transforms your string column into array column

Answer 2

Recently (2020) the JSON_EXTRACT_ARRAY function was added to the bigquery standard sql.

It makes it easy to get the expected behavior with no UDF or tricks

with k as (select JSON_EXTRACT_ARRAY('["a","b","c"]', '$') as x)
select unnested_x from k, unnest(x) unnested_x

Will result in:

╔══════════════╗
║ "unnested_x" ║
╠══════════════╣
║     "a"      ║
║     "b"      ║
║     "c"      ║
╚══════════════╝

JSON_EXTRACT_ARRAY doc