Athena/Presto展开序列化JSON对象列表报错，需转为结构化表格

问题原因 #

你的数据里的JSON对象是被双引号包裹的字符串形式的JSON数组，执行JSON_PARSE后得到的是STRING类型值，而非原生JSON数组。直接尝试将其转为ARRAY<JSON>会触发类型转换错误，因为Athena/Presto无法把字符串直接识别为JSON数组。

解决方案 #

需要先将这个字符串形式的JSON数组重新解析为真正的JSON数组，再展开提取字段：

WITH input AS (
    SELECT JSON_PARSE('"[{"is_flag": 0, "url": "https://app.com/123"},{"is_flag": 1, "url": "https://app.com/456"}]"') AS json_obj
)
SELECT 
    CAST(JSON_EXTRACT(y, '$.is_flag') AS INTEGER) AS is_flag,
    JSON_EXTRACT_SCALAR(y, '$.url') AS url
FROM input
-- 先将字符串解析为JSON数组，再转为ARRAY<JSON>后展开
CROSS JOIN UNNEST(CAST(JSON_PARSE(json_obj) AS ARRAY<JSON>)) AS u(y)

执行结果 #

执行上述SQL后，会得到你期望的结构化表格：

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