记录一个数据拆分案例
select id,userid,
from_unixtime(cast(adddate/1000 as bigint),'yyyy-MM-dd') as adddate,
from_unixtime(cast(updatedate/1000 as bigint),'yyyy-MM-dd') as updatedate,
get_json_object(tag1,'$.id') as setting_id,
if(get_json_object(tag1,'$.enable') = 'true', 1 , 0) as enable
from (
select id,tag1,userid,adddate,updatedate
from (select id,userid,adddate,updatedate,REPLACE(cast(substr(setting,2,length(setting)-2) as String),'},','}#') as aaa from 库名.表名 ) ta
lateral view explode(split(ta.aaa, '#')) r1 as tag1
)acc;
之前数据
拆分之后的数据
21021-07-02更新我的草稿本来了
数组里面的json拆分
insert overwrite table 库名.表名
select
tmp01.companyName,
get_json_object(ccc,'$.position') as position,
get_json_object(ccc,'$.personName') as personName
from(
select
get_json_object(companyBaseInfo,'$.companyName') as companyName,
REPLACE(REPLACE(REPLACE(zhuYaoChengYuan,'[',''),']',''),'},','}###') as tmp
from 库名.表名
)tmp01
lateral view explode(split(tmp01.tmp,'###')) rr as ccc;
原始数据
餐饮管理有限公司 [{“position”: “总经理”, “personName”: “陈1”}, {“position”: “监事”, “personName”: “谢11”}]
拆分之后
companyName position personName
餐饮管理有限公司 执行董事兼总经理 陈1
餐饮管理有限公司 监事 谢11
部分函数应用得当,可拆解正常遇到的多数数据;