如何在DuckDB中将含JSON记录数组的字符串列转为列表列？

问题场景 #

我在使用DuckDB处理Parquet源文件时遇到一个问题：文件中有一列是以字符串编码的JSON记录数组（格式为[{...}, {...}, {...}]），我希望将这列转换为可操作的JSON列表列。尝试用标量函数解析时失败，且无法像read_json_objects那样设置json_format参数，求解决办法。

可复现示例：

-- 创建输入表并插入含JSON记录数组的字符串列
CREATE TABLE input(data VARCHAR); 
INSERT INTO input VALUES ('[{"id": "1", "status":"ok"}, {"id":"2", "status":"ok"}]');

-- 预期的输出表（JSON列表列）
CREATE TABLE output(data JSON);
INSERT INTO output (data) VALUES (ARRAY['{"id": "1", "status": "ok"}', '{"id": "2", "status": "ok"}']);

解决方法 #

1. 直接通过CAST完成类型转换 #

DuckDB支持将合法的JSON格式字符串直接转换为JSON类型，无需复杂配置，一行代码即可实现需求：

-- 将输入表的VARCHAR列转换为JSON列
SELECT CAST(data AS JSON) AS data FROM input;

你可以通过以下语句验证转换结果与预期输出完全一致：

SELECT * FROM (SELECT CAST(data AS JSON) AS data FROM input)
EXCEPT
SELECT * FROM output;
-- 结果为空，说明转换后的数据与预期匹配

2. 使用json_parse显式解析 #

如果需要更明确的解析语义，可以使用json_parse标量函数，效果与CAST完全相同：

SELECT json_parse(data) AS data FROM input;

对转换后的JSON数组操作示例 #

转换完成后，可直接对JSON数组列进行各类操作，比如展开数组中的元素：

-- 展开JSON数组，将每个元素转为单行记录
SELECT json_array_elements(CAST(data AS JSON)) AS record FROM input;

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