json_tuple,get_json_object提取日志埋点信息

现数据库有一张日志表，有个字段存放的json字符串，需要获取orders数组里的is_store_delivery，delivery_warehouse字段

以下是该字段的json数据

{"returnCode":"0","result":{"total":2,"orders":[{"buyer_province":"广东省","transport_time":"","buyer_country_id":"CN","remark":"","transport_no":"05365368605","merged_sn":"","order_status":"23","delivery_kpi_start_time":"2021-09-06 22:31","buyer_mobile":"138****3907","order_goods":[{"vendor_code":"10**06","quantity":1,"size":"均码","price":"147.00","vendor_id":22,"brand_name":"XXXXX","sn":"KYT**-15AW","cooperation_no":"300921","barcode":"6939426","product_name":"迷你电风扇台扇","promotion_price":"","po_no":"21030877"}],"pay_type":"移动微信wap支付","package_received_time":"2021-09-07","transport_day":"送货时间不限","parent_order_sn":"","cod_type":"0","is_forbidden_delivery":0,"is_pre_transport_no":"0","carrier_name":"速运","order_type":1,"buyer_county":"海珠区","cod_money":"0.00","delivery_warehouse":"SPD589","buyer_address":"广东省.广州市.海珠区","buyer_postcode":"5120","vendor_name":"深圳市有限公司","buyer_city":"广州市","buyer":"杨***","merged_code":"","last_update_time":"2021-09-08 10:10:02.0","buyer_tel":"","is_store_delivery":0,"vendor_id":22,"add_time":"2021-09-06 21:58:59.0","order_sn":"21092226","carrier_code":"shunfeng"},{"buyer_province":"广东省","transport_time":"","buyer_country_id":"CN","remark":"","transport_no":"05378847236","merged_sn":"","order_status":"23","delivery_kpi_start_time":"2021-09-07","buyer_mobile":"137****6112","order_goods":[{"vendor_code":"1006","quantity":1,"size":"均码","price":"188.00","vendor_id":22,"brand_name":"XXXXXXXXX","sn":"FSYF","cooperation_no":"3001","barcode":"6934680","product_name":"低噪摇头风扇落地扇","promotion_price":"","po_no":"2103077"}],"pay_type":"微信支付app","package_received_time":"2021-09-07","transport_day":"送货时间不限","parent_order_sn":"","cod_type":"0","is_forbidden_delivery":0,"is_pre_transport_no":"0","carrier_name":"顺丰速运","order_type":1,"buyer_county":"端州区","cod_money":"0.00","delivery_warehouse":"SPD09","buyer_address":"广东省.肇庆市.端州区","buyer_postcode":"520","vendor_name":"深圳市有限公司","buyer_city":"肇庆市","buyer":"黄*","merged_code":"","last_update_time":"2021-09-08","buyer_tel":"","is_store_delivery":0,"vendor_id":22,"add_time":"2021-09-07 12:48:28.0","order_sn":"21083502","carrier_code":"shunfeng"}]}}

对应SQL,获取数组里的值并将其转换为列（行转列）

select  
	distinct
	delivery_warehouse,
	is_store_delivery,
	vendor_id
from
	(SELECT 
	 	split(regexp_replace(regexp_extract(t.json_column, '^\\[(.+)\\]$',1),'\\}\\,\\{', '\\}\\|\\|\\{'),'\\|\\|') AS json_column
	FROM 
    -- 将response替换为上述字符串即可运行
	 	(select get_json_object('上述json字符串', '$.result.orders') as  json_column 
	) t
) a
lateral view explode(json_column) b as j_column
LATERAL VIEW json_tuple(j_column, 'delivery_warehouse', 'is_store_delivery', 'vendor_id') d AS delivery_warehouse, is_store_delivery, vendor_id

 运行效果图：

 获取json数据的思路：
-- (1)json_tuple()获取json脚本 里面json节点orders
-- (2)1)regexp_extract()去掉中括号[]
　 -- 2)regexp_extract()用双竖线替换 },{
    -- 3)split()根据双竖线分割数组为多个小的json
-- (3)json_tuple()获取json下vendor_id等节点值

附加一个presto的写法：

select 
    distinct vendor_id,
    x.delivery_warehouse,
    x.is_store_delivery
from hive.xxx
cross join unnest(cast(json_extract("json字符串", '$.result.orders') as array(row(delivery_warehouse VARCHAR,is_store_delivery INTEGER )))) as x