json_tuple()函数的应用

　　直接举栗子说明用法吧：

　　下面是来自APP埋点脚本获取的一段JSON字符串：通过平台sqoop推数存放在cx_ods_safe.paczcb_paczdata_cz_policy_detail_info.data字段中

{"applicants":[{"birthday":-247478400000,
"certificateNo":"44082419620228443X",
"certificateType":"01",
"customerType":"1",
"name":"方振亚",
"phone":"13692412999",
"sex":"M"}],
"dutyInfoList":[{"dutyCode":"CV01001",
"dutyName":"机动车损失保险",
"insuredAmount":139356.8,
"seats":-999,
"totalActualPremium":1143.71},
{"dutyCode":"CV05002",
"dutyName":"机动车第三者责任保险",
"insuredAmount":1000000.0,
"seats":-999,
"totalActualPremium":1017.67},
{"dutyCode":"CV09003",
"dutyName":"机动车全车盗抢保险",
"insuredAmount":139356.8,
"seats":-999,
"totalActualPremium":406.08},
{"dutyCode":"CV13004",
"dutyName":"机动车车上人员责任保险(司机)",
"insuredAmount":20000.0,
"seats":-999,
"totalActualPremium":42.49},
{"dutyCode":"CV17005",
"dutyName":"机动车车上人员责任保险(乘客)",
"insuredAmount":10000.0,
"seats":4,
"totalActualPremium":54.63},
{"dutyCode":"CV08000",
"dutyName":"玻璃单独破碎险",
"insuredAmount":0.0,
"seats":0,
"totalActualPremium":164.69},
{"dutyCode":"CV27027",
"dutyName":"不计免赔险(机动车损失保险)",
"insuredAmount":0.0,
"seats":-999,
"totalActualPremium":171.56},
{"dutyCode":"CV31028",
"dutyName":"不计免赔险(机动车第三者责任保险)",
"insuredAmount":0.0,
"seats":-999,
"totalActualPremium":152.65},
{"dutyCode":"CV41048",
"dutyName":"不计免赔险(机动车全车盗抢险)",
"insuredAmount":0.0,
"seats":-999,
"totalActualPremium":81.22},
{"dutyCode":"CV44049",
"dutyName":"不计免赔险(机动车车上人员责任保险(司机))",
"insuredAmount":0.0,
"seats":-999,
"totalActualPremium":6.37},
{"dutyCode":"CV49063",
"dutyName":"机动车损失保险无法找到第三方特约险",
"insuredAmount":0.0,
"seats":-999,
"totalActualPremium":28.59},
{"dutyCode":"CV44080",
"dutyName":"不计免赔险(机动车车上人员责任保险(乘客))",
"insuredAmount":0.0,
"seats":-999,
"totalActualPremium":8.19}],

"insurants":[{"address":"广东省雷州市西湖大道79号25栋605房",
"birthday":-247478400000,
"certificateNo":"44082419620228443X",
"certificateType":"01",
"customerType":"1",
"name":"方振亚",
"phone":"13692412999","sex":"M"}],

"targets":[{"autoModelName":"炫威DHW7183RUCRE轿车",
"engineNo":"2044185",
"exhaustCapability":"1.799",
"owner":{"address":"广东省广州市天河区请到附近门店自取",
"birthday":-247478400000,
"certificateNo":"44082419620228443X",
"certificateType":"01",
"customerType":"1",
"name":"方春",
"phone":"18922074876",
"sex":"M"},
"ownershipAttributeCode":"03",
"registerDate":1435680000000,
"usageAttributeCode":"02",
"vehicleFrameNo":"LVHRU5804F6044188",
"vehicleLicenseCode":"粤G-JB877",
"vehicleSeats":"5",
"vehicleTonnages":"0.0"}]}；

 

获取json数据的思路：
-- (1)json_tuple()获取json脚本 里面json节点dutyInfoList
-- (2)1)regexp_extract()去掉中括号[]
　 -- 2)regexp_extract()用双竖线替换 },{
    -- 3)split()根据双竖线分割数组为多个小的json
-- (3)json_tuple()获取json下dutyCode，insuredAmount等节点值
-- 其他节点的字段也可在同一个sql上取，只是每行相同且根据节点dutyInfoList确定行数

 

INSERT OVERWRITE TABLE DM_PACZ_APPLY_POLICY_DUTYINFOLIST
SELECT current_date AS stat_date
　　　　, a.apply_policy_no
　　　　, d.dutycode
　　　　, NVL(d.insuredamount, 0) AS insuredamount
FROM ${OdsSafeDatabase}.PACZCB_PACZDATA_CZ_POLICY_DETAIL_INFO a
LATERAL VIEW json_tuple(data, 'dutyInfoList') b AS dutyinfolist
LATERAL VIEW explode(split(regexp_replace(regexp_extract(b.dutyinfolist,'^\\[(.+)\\]$',1),'\\}\\,\\{', '\\}\\|\\|\\{'),'\\|\\|')) c AS list
LATERAL VIEW json_tuple(c.list, 'dutyCode', 'insuredAmount') d AS dutycode, insuredamount
;