如何在BigQuery中将字符串化数组转换为数组? [英] How to convert stringified array into array in BigQuery?

问题描述

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

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

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

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

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

我已经尝试过JSON_EXTRACT('["a","b","c"]','$')以及可以在网上找到的所有其他内容.

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

有什么想法吗?

推荐答案

以下是BigQuery标准SQL

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

这篇关于如何在BigQuery中将字符串化数组转换为数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！