hive 中解析json

hive中解析json常用的方式按效率由低到高有三种：

• regexp_extract
• get_json_object
• json_tuple

demo数据：

set hivevar:person={"name":"amos","sex":"man","age":32};

1.regexp_extract 解析，这种方式很不友好，需要数据有固定的格式

select regexp_extract(regexp_replace('${hivevar:person}','\"',''),'name:(.*),sex',1) as name;
OK
name
amos

2.get_json_object 解析

select get_json_object('${hivevar:person}','$.name') as name,get_json_object('${hivevar:person}','$.sex') as sex,get_json_object('${hivevar:person}','$.age') as age;
OK
name	sex	age
amos	man	32

3.json_tuple解析

with json_table as(select '${hivevar:person}' as person)
select person.* from json_table lateral view json_tuple(person,'name','sex','age') person as name,sex,age;
OK
person.name	person.sex	person.age
amos	man	32