json tuple explode

HIVE: lateral view explode & json_turpe 实现 json数组行转列&字段拆分 - 简书

----- 司机ID

select orderId

,keys_1

,value_1

,split(regexp_replace(value_1,'\\[\\|\\]',''),',')[0] v0

,split(regexp_replace(value_1,'\\[\\|\\]',''),',')[1] v1

,split(regexp_replace(value_1,'\\[\\|\\]',''),',')[2] v2

,split(regexp_replace(value_1,'\\[\\|\\]',''),',')[3] v3

,split(regexp_replace(value_1,'\\[\\|\\]',''),',')[4] v4

from (

select

get_json_object(data,'$.orderId') orderId

,get_json_object(data,'$.improveDrivers') json0

from dj_dw.odsxxxlog

where pt='2021-12-19' and cmd='30xxx0'

) tt

lateral view explode(str_to_map(substring(json0,2,length(json0)-2))) t1 as keys_1,value_1;

-----OK的脚本

select orderId , info.*

from

(

select orderId,concat('{',entity,'}') as coupons

from

(

select get_json_object(data,'$.orderId') orderId

,get_json_object(data,'$.orientStrategies') infos

from dj_dw.odxxxxg

where pt='2021-12-19' and cmd='30xx0'

) a

lateral view explode(split(substring(infos,3,length(infos)-4),'\\},\\{')) b as entity

) source

lateral view json_tuple(coupons,'id','name') info

as id,name ;