直接进入主题:我的需求是:
将Json字符串中含Array的字段的字符串提取出来;将Array中需要的指定字段展示出来组合输出一个字符串;
原数据如下:
处理逻辑如下hive-sql:
select tmp.id ,concat_ws(',',collect_set(tmp.channelSet)) channelName
from (
select id,
get_json_object(
case when ss.col regexp '^\\{' and not ss.col regexp '\\}$' then concat(ss.col,'\}')
when not ss.col regexp '^\\{' and ss.col regexp '\\}$' then concat('\{',ss.col)
when ss.col regexp '^\\{' and ss.col regexp '\\}$' then ss.col
end ,"$.channelName") channelSet
from (
select id,split(regexp_extract(a.channelSet,'^\\[(.+)\\]$',1),'\\}\\,\\{') as str
from
(
select id, get_json_object(global_config,"$.channelSet") as channelSet from ods.rc_xxxx_config where day = '2020-09-10' and seq_no = 'zvy837Z' order by id desc
) a ) pp
lateral view explode(pp.str) ss as col
) tmp group by tmp.id;
输出为: