Presto报错CANNOT UNNEST TYPE: VARCHAR，求解析无分隔符字符串方案

问题根源 #

你执行的SQL报错是因为eventdata是字符串类型（VARCHAR），而UNNEST仅适用于数组/集合类型，无法直接拆分字符串。另外你的目标字段是嵌在非标准JSON结构里的，必须先把字符串转换成合法JSON再提取字段。

具体实现代码 #

SELECT
    expe_property_id,
    eventdate,
    eventname,
    -- 从解析后的JSON中提取各字段
    JSON_EXTRACT_PATH_TEXT(standard_json, 'actionType') AS action_type,
    JSON_EXTRACT_PATH_TEXT(standard_json, 'propertyStatus') AS property_status,
    JSON_EXTRACT_PATH_TEXT(standard_json, 'feature') AS feature,
    JSON_EXTRACT_PATH_TEXT(standard_json, 'hotelID') AS hotel_id,
    JSON_EXTRACT_PATH_TEXT(standard_json, 'userCode') AS user_code,
    JSON_EXTRACT_PATH_TEXT(standard_json, 'providerStatus') AS provider_status,
    JSON_EXTRACT_PATH_TEXT(standard_json, 'statusCode') AS status_code
FROM (
    SELECT
        expe_property_id,
        eventdate,
        eventname,
        -- 将非标准字符串转换为合法JSON
        PARSE_JSON(
            REGEXP_REPLACE(
                REGEXP_REPLACE(eventdata, '^\\{7 items ', '\\{'),
                '" (\\w+):"', '", "\\1":"'
            )
        ) AS standard_json
    FROM bexg_prod_project_arm_report.apm_bulk
) t

代码说明 #

1. 清理非标准格式：
   • 用REGEXP_REPLACE(eventdata, '^\\{7 items ', '\\{')移除开头无效的{7 items 标识
   • 用REGEXP_REPLACE(..., '" (\\w+):"', '", "\\1":"'把键值对之间的空格替换为逗号，确保值内部的空格（如Alexandre Giard）不受影响
2. 解析并提取字段：
   • PARSE_JSON将清理后的字符串转为JSON对象
   • JSON_EXTRACT_PATH_TEXT（或部分引擎支持的standard_json:actionType::STRING语法）直接提取目标字段

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