如何在Presto中UNNEST SQL列内可变的JSON键值对列表

你的错误原因是：JSON对象本质是键值映射（Map），而非数组，直接将其转为ARRAY(ROW(...))类型不符合数据结构，导致转换失败。

正确的做法是先将JSON解析为MAP类型，再通过UNNEST展开键值对：

基础示例（值均为字符串） #

WITH example(json_info) AS (
    VALUES ('{"Key A": "ABC","Key B": "DEF", "Key C": "XYZ"}') 
)
SELECT 
    map_key AS key, 
    map_value AS value
FROM example
CROSS JOIN UNNEST(CAST(JSON_PARSE(json_info) AS MAP(VARCHAR, VARCHAR))) AS x(map_key, map_value);

处理多类型值的情况 #

如果JSON中值的类型不固定，可以先转为MAP(VARCHAR, JSON)，再按需转换值的类型：

WITH example(json_info) AS (
    VALUES ('{"Key A": "ABC","Key B": 123, "Key C": true}') 
)
SELECT 
    map_key AS key, 
    map_value,
    -- 按类型提取值
    CASE WHEN JSON_TYPE(map_value) = 'STRING' THEN JSON_EXTRACT_SCALAR(map_value, '$') END AS string_value,
    CASE WHEN JSON_TYPE(map_value) = 'NUMBER' THEN JSON_EXTRACT_SCALAR(map_value, '$')::INT END AS int_value,
    CASE WHEN JSON_TYPE(map_value) = 'BOOLEAN' THEN JSON_EXTRACT_SCALAR(map_value, '$')::BOOLEAN END AS bool_value
FROM example
CROSS JOIN UNNEST(CAST(JSON_PARSE(json_info) AS MAP(VARCHAR, JSON))) AS x(map_key, map_value);

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