如何在Hive中将字符串格式的map数组解析并转换为单层map

方案一：Hive 2.3及以上版本（推荐） #

直接使用Hive内置from_json函数解析JSON字符串为指定格式的结构体数组，结合map_agg聚合生成目标map，最后转JSON字符串即可，性能更高语法更简洁。
完整实现SQL：

WITH test_data AS (
    -- 示例测试数据
    select 1 as user_id, '[{"field":"name", "value":"Bob"}, {"field":"gender", "value":"M"}]' as user_info
    union all
    select 2 as user_id, '[{"field":"gender", "value":"F"}, {"field":"age", "value":22}, {"field":"name", "value":"Ana"}]' as user_info
)
SELECT 
    user_id,
    to_json(map_agg(item.field, item.value)) AS user_info
FROM test_data
-- 解析JSON字符串为结构体数组并炸开
LATERAL VIEW explode(from_json(user_info, 'array<struct<field:string,value:string>>')) t AS item
GROUP BY user_id;

如果不需要输出JSON字符串，只要map类型结果，去掉外层to_json函数即可。

方案二：低版本Hive兼容方案 #

如果Hive版本低于2.3不支持from_json，可以用正则处理+字符串分割的方式解析数组元素，再提取对应字段：

WITH test_data AS (
    select 1 as user_id, '[{"field":"name", "value":"Bob"}, {"field":"gender", "value":"M"}]' as user_info
    union all
    select 2 as user_id, '[{"field":"gender", "value":"F"}, {"field":"age", "value":22}, {"field":"name", "value":"Ana"}]' as user_info
)
SELECT 
    user_id,
    concat("{", concat_ws(",", collect_list(concat('"', field, '":"', value, '"'))), "}") AS user_info
FROM (
    SELECT
        user_id,
        get_json_object(item_json, '$.field') AS field,
        get_json_object(item_json, '$.value') AS value
    FROM test_data
    -- 去除数组前后括号，分割得到单个元素字符串
    LATERAL VIEW explode(split(regexp_replace(user_info, '^\\[|\\]$', ''), '}, \\{')) t AS item_str
    -- 补全JSON格式的大括号
    LATERAL VIEW (SELECT concat("{", regexp_replace(item_str, '^\\{|\\}$', ''), "}")) tmp AS item_json
) t
GROUP BY user_id;

注意：该方案依赖JSON格式规范，若value内容中包含}, {特征字符串会出现解析错误，仅适合格式统一的日志场景使用。

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