hive解析jsonb

get_json_object(string json_string, string path)

说明：
第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL。
每次只能返回一个数据项。

举例：
data 为 test表中的字段，数据结构如下：

data =
{
 "store":
        {
         "fruit":[{"weight":8,"type":"apple"}, {"weight":9,"type":"pear"}],  
         "bicycle":{"price":19.95,"color":"red"}
         }, 
 "email":"amy@only_for_json_udf_test.net", 
 "owner":"amy" 
}

1.get单层值

hive> select  get_json_object(data, '$.owner') from test;
结果：amy

2.get多层值.

hive> select  get_json_object(data, '$.store.bicycle.price') from test;
结果：19.95

3.get数组值[]

hive> select  get_json_object(data, '$.store.fruit[0]') from test;
结果：{"weight":8,"type":"apple"}

数组+json解析

例子

{"personal_solvency":
{"outside_pengyuan_personal_solvency":
{"result":"{\"receiveTime\":\"20190712 11:45:11\",\"cisReport\":
[{\"reportID\":\"2019071211063117\",\"queryReasonID\":\"101\",\"subReportTypes\":\"16000\",\"personAnnualIncome\":{\"income\":\"5.9-6.5\",\"subReportTypeCost\":\"16000\",\"subReportType\":\"16000\",\"errorMessage\":\"\",\"treatResult\":\"1\"},\"hasSystemError\":\"false\",\"treatResult\":\"2\",\"buildEndTime\":\"2019-07-12 11:45:11\",\"isFrozen\":\"false\",\"subReportTypesShortCaption\":\"1、个人偿债能力分析(16000)\"}],\"batNo\":\"2019071211063109\",\"unitName\":\"杭州义牛网络技术有限公司\",\"queryUserID\":\"ynwlwsqueryn01\",\"subOrgan\":\"金融技术部/数据支持组\",\"queryCount\":\"1\"}"}},"pengyuan":{"outside_pengyuan_score":{"unit_name":"杭州义牛网络技术有限公司","query_user_id":"ynwlwsqueryn01","bat_no":"2019071211063126","outside_cis_report":[{"person_apply_score_info_sub_report_type":"15105","outside_query_conditions":[{"name":"name","caption":"被查询者姓名","value":"2$1$AAADtpnSP1DHSruSuKfXIfWHKlU="},{"name":"documentNo","caption":"被查询者证件号码","value":"2$1$AAADl2i9tr8mB67jlKSQPhqC08XlL3PVDtpYpsWzaGhSlp+Q"},{"name":"phone","caption":"手机号码","value":"2$1$AAADvPBaKyLR/wl/XFHkrXW9H4o="}],"sub_report_types":"15105","is_frozen":"false","treat_result":"2","query_reason_id":"101","person_apply_score_info_score":"164","person_apply_score_info_treat_result":"1","build_end_time":"2019-07-12 11:45:12","has_system_error":"false","report_id":"2019071211063134","person_apply_score_info_advice":"建议人工审批","person_apply_score_info_sub_report_type_cost":"15105","sub_report_types_short_caption":"1、DR模型(15105)","person_apply_score_info_grade":"中风险","person_apply_score_info_error_message":""}],"query_count":"1","receive_time":"20190712 11:45:12","sub_organ":"金融技术部/数据支持组","version":1}}}

解析出income

select *,get_json_object(aa, '$.personAnnualIncome.income') as income
from(
select output,a,get_json_object(a, '$.cisReport[0]') as aa
from (
select *,get_json_object(output, '$.personal_solvency.outside_pengyuan_personal_solvency.result') as a
from userdb.py_test_200_not_loan_result
where output is not null
)t0)t0

---

参考：https://blog.csdn.net/qq_34105362/article/details/80454697