hivesql解析json格式的key与value

json格式示例：
{“city_code”:“340100”,“county_code”:“340111”,“orientation”:“东”,“road_id”:35204271,“speed”:35.72}

解析json格式中的key

我们可以看到这个数据还是很规整的，首先先将这个json数据中的花括号：“{}”给去除，并将逗号：“，”也都给替换成冒号：“：”，双引号也给剔除，之后进行行列转换，选取需要的
1、剔除双引号、花括号
select translate(’{“city_code”:“340100”,“county_code”:“340111”,“orientation”:“东”,“road_id”:35204271,“speed”:35.72}’,’{}""’,’’)

2、逗号替换成冒号
select
regexp_replace(
translate(’{“city_code”:“340100”,“county_code”:“340111”,“orientation”:“东”,“road_id”:35204271,“speed”:35.72}’,’{}""’,’’),’,’,’:’
)

3、行列转换
select pos+1,val
from
(
select
regexp_replace(
translate(’{“city_code”:“340100”,“county_code”:“340111”,“orientation”:“东”,“road_id”:35204271,“speed”:35.72}’,’{}""’,’’),’,’,’:’
) str
) t lateral view posexplode(split(str,’:’)) t1 as pos,val

4、选取奇数行
select rn,val as key
from
(
select pos+1 as rn,val
from
(
select
regexp_replace(
translate(’{“city_code”:“340100”,“county_code”:“340111”,“orientation”:“东”,“road_id”:35204271,“speed”:35.72}’,’{}""’,’’),’,’,’:’
) str
) t lateral view posexplode(split(str,’:’)) t1 as pos,val
) t3
where rn%2=1

解析json格式中的value

1、get_json_object()函数
语法：get_json_object(json_string, ‘$.key’)
说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。这个函数每次只能返回一个数据项。

select
get_json_object(’{“city_code”:“340100”,“county_code”:“340111”,“orientation”:“东”,“road_id”:35204271,“speed”:35.72}’,’ . r o a d i d ′ ) , g e t j s o n o b j e c t ( ′ " c i t y c o d e " : " 340100 " , " c o u n t y c o d e " : " 340111 " , " o r i e n t a t i o n " : " 东 " , " r o a d i d " : 35204271 , " s p e e d " : 35.72 ′ , ′ .road_id'), get_json_object('{"city_code":"340100","county_code":"340111","orientation":"东","road_id":35204271,"speed":35.72}',' .roadi​d′),getj​sono​bject(′"cityc​ode":"340100","countyc​ode":"340111","orientation":"东","roadi​d":35204271,"speed":35.72′,′.speed’)

2、json_tuple()函数

感谢阅读，我是啊帅和和，一位大数据专业大四学生，祝你快乐。