json 转多行
2021-05-30 09:00
标签：length class tab _id rom ext use sub lan json字段格式 json 转多行 标签：length class tab _id rom ext use sub lan 原文地址：https://www.cnblogs.com/blogabc/p/14676090.html{
"a": {
"10000": 11,
"20000": 22
},
"b": {
"10000": 14,
"20000": 255
}
}
with
q as (
select t.member_id,
replace(t.prod_code, ‘"‘) as prod_code ,
replace(replace(t.score_use, "{"), ‘}‘) as score_use
from (
select member_id,prod_code, score_use
from (select member_id, extra_json from ods_tables)
lateral view explode(
str_to_map(
substr(extra_json, 2, length(extra_json) -2)
, ‘},"‘
)
) t as prod_code, score_use
) t
)
select q.member_id, tmp.gcode, tmp.score from q lateral view explode(str_to_map(q.score_use)) tmp as gcode,score