google-bigquery - 如何在BigQuery中将字符串化数组转换为数组？

Question

碰巧我在BigQuery的字段中有一个字符串化数组

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

我想将其转换为BigQuery可以理解的数组。
我希望能够在标准SQL中执行此操作：

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

我已经尝试过 JSON_EXTRACT('["a","b","c"]','$')和其他所有我可以在网上找到的东西。

有任何想法吗？

Answer 1

以下是BigQuery标准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

它将您的字符串列转换为数组列