Presto查询JSON列展开Map/Array时空值报错问题咨询

• 第一次报错：当key-array/value-array字段值为空字符串时，json_parse接收空串入参会返回null，后续强转array(varchar)时检测到输入为null，触发"Expected a json array, but got null"错误。
• 第二次修改后报错：coalesce函数只有在第一个参数返回null时才会触发 fallback 逻辑，但json_parse('')接收到空字符串时会直接抛出异常，不会返回null，因此coalesce完全没生效；同时原查询存在字段名书写错误，JSON中字段为key-array/value-array，查询里写的是key-arr/val-arr，也会导致字段取值异常。

在调用json_parse之前，先对空串、null类非法输入做预处理，替换为合法的JSON数组字符串，再做解析和类型转换，避免非法输入触发函数异常。参考可直接运行的SQL如下：

select key, 
    value
from ( 
    select cast(json_parse(json_payload) as map(varchar, json)) parsed 
    from my_table
) a, 
unnest(
    cast(
        json_parse(
            case 
                when cast(parsed['key-array'] as varchar) in ('', 'null') or parsed['key-array'] is null then '[]'
                else cast(parsed['key-array'] as varchar) 
            end
        ) as array(varchar)
    ), 
    cast(
        json_parse(
            case 
                when cast(parsed['value-array'] as varchar) in ('', 'null') or parsed['value-array'] is null then '[]'
                else cast(parsed['value-array'] as varchar) 
            end
        ) as array(varchar)
    )
) as t(key, value)

• 上述写法对空值场景默认返回空数组[]，unnest处理空数组时不会产出任何行，符合空键值对列表不展开的预期。如果需要保留空行，可将默认值替换为符合业务需求的合法JSON数组字符串，例如'[null]'。
• 不要将空字符串传入json_parse函数，该函数仅接受合法JSON格式的字符串入参，空串、格式错误的字符串都会直接触发运行时异常，必须在函数调用前完成非法值过滤。

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