1、语法：regexp_replace(subject,pattern,str)
注：subject,pattern,str都为字符串，subject为被替换的字符串，pattern为正则表达式，str需要替换的字符串。
2、表描述：表explode_test，表头（area,good_id,sale_info）
a:shangdong,b:beijing,c:shanghai 1,2,3 [{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jdmart","monthSales":7900,"userCount":2900,"score":"5.9"},{"source":"yam","monthSales":54900,"userCount":12900,"score":"4.9"}]a:tianjing,b:beijing,c:shanghai 3,4,5 [{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jdmart","monthSales":7900,"userCount":2900,"score":"5.9"},{"source":"yam","monthSales":54900,"userCount":12900,"score":"4.9"}]
3、去掉sale_info的【】
select regexp_replace(sale_info, '\\[ | \\]' , '') —— “|”是或的意思
from explode_test;
4、切分取出数据
select explode(split(regexp_replace(sale_info, '\\[\\{|\\}\\]' ), '\\},\\{' ))
from explode_test;
5、变成结构化表
selectsplit(split(a.col,',')[0],':')[1] as source,split(split(a.col,',')[1],':')[1] as monthSales,split(split(a.col,',')[2],':')[1] as userCount,split(split(a.col,',')[3],':')[1] as scorefrom(select explode(split(regexp_replace(sale_info,'\\[\\{|\\}\\]',''),'\\},\\{')) as col from explode_test) a;
6、get_json_object实现
将数据变成字典形式，然后用get_json_object提取数据。
select
get_json_object ( a.col, '$.source' ) as source,
get_json_object (a.col, '$.monthSales' ) as monthSales,
get_json_object (a.col, '$.userCount' ) asuserCount,
get_json_object (a.col, '$.score' ) asscore
from
(select
explot(split(regexp_replace(regexp_replace(sale_info, '\\[|\\]',''),, '\\}\\,\\{', '\\}\\;\\{'), '\\;')) as col
from explode_test) a;
如果觉得《hive 正则表达替换函数 regexp_replace》对你有帮助，请点赞、收藏，并留下你的观点哦！
