如何在BigQuery中将JSON转换为数组？解决CAST转换报错问题

BigQuery不支持直接通过CAST将JSON类型（或JSON字符串）转换为ARRAY类型，需要使用专门的JSON处理函数来实现。以下是针对不同场景的解决方案：

1. 处理JSON字符串形式的数组 #

如果你的输入是像'[1, 2]'这样的JSON字符串，使用JSON_VALUE_ARRAY函数解析后再转换类型：

SELECT CAST(JSON_VALUE_ARRAY('[1, 2]') AS ARRAY<INTEGER>) AS int_array;

JSON_VALUE_ARRAY会将JSON字符串解析为字符串数组，再通过CAST转换为整数数组即可。

2. 处理JSON类型字段中的数组 #

如果你的数据存储在JSON类型的字段中（比如字段名为json_data，类型为JSON），可以先提取数组再转换：

SELECT CAST(JSON_EXTRACT_ARRAY(json_data) AS ARRAY<INTEGER>) AS int_array
FROM your_table;

3. 提取嵌套JSON中的数组 #

如果需要从嵌套JSON结构中提取数组（比如'[{"id":1},{"id":2}]'），可以指定JSON路径来提取：

SELECT CAST(JSON_VALUE_ARRAY('[ {"id":1}, {"id":2} ]', '$.id') AS ARRAY<INTEGER>) AS id_array;

关键说明 #

• JSON_VALUE_ARRAY：用于从JSON字符串或JSON类型中提取顶层数组，或指定路径提取嵌套数组，返回ARRAY类型。
• JSON_EXTRACT_ARRAY：用于从JSON类型中提取数组，返回ARRAY类型，之后可通过CAST转换为目标数据类型。

内容的提问来源于stack exchange，提问作者David542