一、原始数据
acctcontent1232313[{"name":"张三","code":"上海浦东新区89492jfkdaj\r\n福建的卡"...},{"name":"狂徒","code":"select * from table where aa=1\r\n and a=12"...},{...}]......
二、需求
上述数据表名code_content,把json中code内容全都取出来拼接成一行数据,最终效果:
acctnew_content1232313上海浦东新区89492jfkdaj\r\n福建的卡\u0001select * from table where aa=1\r\n and a=12......
三、解析思路
四、实现方法
1.sql
select acct,concat_ws('\u0001',collect_list(t.code)) code
from
(
select acct,get_json_object(a_json,'$.code') code
from
(
select acct,
split(regexp_replace(regexp_extract(code,'(^\\[)(.*?)(\\]$)',2),'\\},\\{','\\}|\\{'),'\\|') as t_code
from code_content
where dt=20230823
) a
lateral view explode(t_code) code_content_tab as a_json
) t
group by acct
2.sql解释:
regexp_extract(code,'(^\\[)(.*?)(\\]$)',2):用正则取出数组里的jsonregexp_replace:替换},{为}|{,方便切割因为,号一般语句里会比较多split:切割成数组explode:函数中的参数传入的是arrary数据类型的列名,通常,explode函数会与lateral view一起结合使用lateral view:Lateral View配合 split, explode 等UDTF函数一起使用,它能够将一列数据拆成多行数据,并且对拆分后结果进行聚合,即将多行结果组合成一个支持别名的虚拟表。get_json_object(a_json,'$.code'):获取json字段valueconcat_ws('\u0001',collect_list(t.code)):“列转行”
参考:
Hive SQL中的 lateral view 与 explode(列转行)以及行转列_sql explode_卖山楂啦prss的博客-CSDN博客
hivesql解析json数组并拆分成多行_hive sql怎么对一个数组进行分行_Time Woods的博客-CSDN博客
推荐阅读
发表评论