hive解析json数组_hivejson数组解析

前言 解析如下数组 获取app_name和 app_type 字段

[{"app_name":"Kloningponsel","app_type":1,"in_time":1544554257000,"package":"com.coloros.backuprestore","version_name":"5.0.32","flags":954744389,"version_code":5032,"up_time":1565434586000},{"app_name":"AdaKami","app_type":0,"in_time":1579322813871,"package":"com.idnada.loan","version_name":"1.7.1","flags":954744388,"version_code":29,"up_time":1581659506373},{"app_name":"com.factory.mmigroup.MMIGroupApplication","app_type":1,"in_time":1544554257000,"package":"com.factory.mmigroup","version_name":"2.1","flags":948485709,"version_code":3,"up_time":1544554257000}]

1、先通过正则表达式将数组中的',' 用 ';' 代替

不替换后续拆分会出现问题

select regexp_replace('[{"app_name":"Kloningponsel","app_type":1,"in_time":1544554257000,"package":"com.coloros.backuprestore","version_name":"5.0.32","flags":954744389,"version_code":5032,"up_time":1565434586000},{"app_name":"AdaKami","app_type":0,"in_time":1579322813871,"package":"com.idnada.loan","version_name":"1.7.1","flags":954744388,"version_code":29,"up_time":1581659506373},{"app_name":"com.factory.mmigroup.MMIGroupApplication","app_type":1,"in_time":1544554257000,"package":"com.factory.mmigroup","version_name":"2.1","flags":948485709,"version_code":3,"up_time":1544554257000}]', '\\}\\,\\{','\\}\\;\\{');

结果如下：

 

2、再通过正则表达式把数组中的[]去掉

select regexp_replace(
regexp_replace(
'[{"app_name":"Kloningponsel","app_type":1,"in_time":1544554257000,"package":"com.coloros.backuprestore","version_name":"5.0.32","flags":954744389,"version_code":5032,"up_time":1565434586000},{"app_name":"AdaKami","app_type":0,"in_time":1579322813871,"package":"com.idnada.loan","version_name":"1.7.1","flags":954744388,"version_code":29,"up_time":1581659506373},{"app_name":"com.factory.mmigroup.MMIGroupApplication","app_type":1,"in_time":1544554257000,"package":"com.factory.mmigroup","version_name":"2.1","flags":948485709,"version_code":3,"up_time":1544554257000}]', '\\}\\,\\{','\\}\\;\\{'
),'\\[|\\]',''
);

3、用split函数按;进行分割

select 
split(
regexp_replace(
regexp_replace(
'[{"app_name":"Kloningponsel","app_type":1,"in_time":1544554257000,"package":"com.coloros.backuprestore","version_name":"5.0.32","flags":954744389,"version_code":5032,"up_time":1565434586000},{"app_name":"AdaKami","app_type":0,"in_time":1579322813871,"package":"com.idnada.loan","version_name":"1.7.1","flags":954744388,"version_code":29,"up_time":1581659506373},{"app_name":"com.factory.mmigroup.MMIGroupApplication","app_type":1,"in_time":1544554257000,"package":"com.factory.mmigroup","version_name":"2.1","flags":948485709,"version_code":3,"up_time":1544554257000}]', '\\}\\,\\{','\\}\\;\\{'
),'\\[|\\]',''
),'\\;'
);

 

4、再利用explode函数将数组转换按列输出

select explode(
split(
regexp_replace(
regexp_replace(
'[{"app_name":"Kloningponsel","app_type":1,"in_time":1544554257000,"package":"com.coloros.backuprestore","version_name":"5.0.32","flags":954744389,"version_code":5032,"up_time":1565434586000},{"app_name":"AdaKami","app_type":0,"in_time":1579322813871,"package":"com.idnada.loan","version_name":"1.7.1","flags":954744388,"version_code":29,"up_time":1581659506373},{"app_name":"com.factory.mmigroup.MMIGroupApplication","app_type":1,"in_time":1544554257000,"package":"com.factory.mmigroup","version_name":"2.1","flags":948485709,"version_code":3,"up_time":1544554257000}]', '\\}\\,\\{','\\}\\;\\{'
),'\\[|\\]',''
),'\\;'
)
);

 

5、最后用json_tuple函数将explode转换的json格式的列进行解析

select json_tuple(json, 'app_name', 'app_type') 
from 
    (
    select explode(
    split(
    regexp_replace(
    regexp_replace(
'[{"app_name":"Kloningponsel","app_type":1,"in_time":1544554257000,"package":"com.coloros.backuprestore","version_name":"5.0.32","flags":954744389,"version_code":5032,"up_time":1565434586000},{"app_name":"AdaKami","app_type":0,"in_time":1579322813871,"package":"com.idnada.loan","version_name":"1.7.1","flags":954744388,"version_code":29,"up_time":1581659506373},{"app_name":"com.factory.mmigroup.MMIGroupApplication","app_type":1,"in_time":1544554257000,"package":"com.factory.mmigroup","version_name":"2.1","flags":948485709,"version_code":3,"up_time":1544554257000}]', '\\}\\,\\{','\\}\\;\\{'
),'\\[|\\]',''
),'\\;'
)
) as json) a;