如何在 HIVE 中将字符串解析为映射数组

我有一个从系统日志中提取的配置单元表。数据以一种奇怪的格式（映射数组）编码，其中数组的每个元素都包含field_name和它的value。列类型为 STRING。就像在下面的例子中一样：

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;

请注意，数组大小并不总是相同的。我正在尝试将map数组转换为简单map。然后，下面就是我期望的结果：

用户身份	用户信息
1	{“name”:“Bob”, “gender”:“M”}
2	{“name”:“Ana”, “gender”:“F”, “age”:22}

实现方式

查看代码中的注释。要转换为映射的字符串数组由此产生split(user_info, ‘(?<=\}) *, *(?=\{)’)。然后它被分解，每个元素都转换为map。

with mydata 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,
       --build new map
       str_to_map(concat('name:', name, nvl(concat(',','gender:', gender),''),  nvl(concat(',','age:', age),'') )) as user_info
from 
(
	select user_id, 
	      --get name, gender, age, aggregate by user_id
	      max(case when user_info['field'] = 'name' then user_info['value'] end) name,
	      max(case when user_info['field'] = 'gender' then user_info['value'] end) gender,
	      max(case when user_info['field'] = 'age' then user_info['value'] end) age
	      
	from      
	(
		select s.user_id, 
		       --remove {} and ", convert to map
		       str_to_map(regexp_replace(e.element,'^\\{| *"|\\}$','')) as user_info 
		from
		(
			select 
				user_id, regexp_replace(user_info, '^\\[|\\]$','') as user_info -- remove []
		 	from mydata
		)s 
		lateral view outer explode(split(user_info, '(?<=\\}) *, *(?=\\{)'))e as element 
		--split by comma between }{ with optional spaces in between
	) s
	group by user_id
)s

结果:

user_id   user_info 
1        {"name":"Bob","gender":"M"}
2        {"name":"Ana","gender":"F","age":"22"}

上面的方式有个缺点，如果属性比较多的话，就要获取很多个属性字段，可以稍微做下调整
将{} 以及 “” 和 field 、value等字段使用 regexp_replace 替换掉，拼接成类似 name:aaa,gender:F,age:22 的字符串
然后使用str_to_map函数即可