hive实现json数组拆解

PAYCHANNELDETAIL表中间字段acquire_type是json格式如下 

Json代码 

 

1. [{"payAmount":"375000","payChannelCode":"BOC"},{"payAmount":"376000","payChannelCode":"ABC"}]  

下面是数据拆解操作 

Sql代码 

 

1.  select pay_order_id,tag1,bill_date  
2. from (  
3. select pay_order_id,substr(acquire_type,2,length(acquire_type)-2) tags,bill_date from dd.PAYCHANNELDETAIL  
4. ) ta  lateral view explode(split(tags, '},')) r1 as tag1  
5. ;  

查询结果如下: 

Java代码 

 

1. 1603150000007617360 {"payAmount":"375000","payChannelCode":"BOC"}   2016-03-15  
2. 1603150000007617360 {"payAmount":"376000","payChannelCode":"ABC"}   2016-03-15  

json属性拆解 

Sql代码 

 

1. SELECT   
2. pay_order_id,  
3. get_json_object(subjson,'$.payAmount') as payAmount,  
4. get_json_object(subjson,'$.payChannelCode') as payChannelCode,  
5. bill_date  
6. FROM  
7. dd.PAYCHANNELDETAIL_SUBJSON 

参考：https://xly1981.iteye.com/blog/2308643