Hivesql中将数组字符串Array，将指定属性的值组合成一个字符串，输出(concat_ws,collect_set,get_json_object,regexp,......)

直接进入主题：我的需求是：

将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;

输出为：