SQL进阶技巧：如何将字符串数组清洗为简单map结构? | translate + regexp_replace方法

目录

0 场景描述

1 数据准备

2 问题分析

2.1 方法1 特征法-通用解法

 2.2 方法2枚举法（不通用）

3 小结

 ~~END~~

  如果觉得本文对你有帮助，那么不妨也可以选择去看看我的博客专栏 ，部分内容如下：

数字化建设通关指南专栏原价99，现在活动价29.9，按照阶梯式增长，直到恢复原价

---

0 场景描述

假如有一个从系统日志中提取的配置单元表，数据以一种奇怪的格式（映射数组）编码，其中数组的每个元素都包含field_name和它的value。列类型为 STRING。就像在下面的例子中一样：

 

请注意，数组大小并不总是相同的。我正在尝试将map数组转换为简单map。然后，下面就是我期望的结果：

用户身份	用户信息
1	{“name”:“Bob”, “gender”:“M”}
2	{“name”:“Ana”, “gender”:“F”, “age”:22}

 

1 数据准备

create table sys_log as (select 1                                                                    as user_id,
                                '[{"field":"name", "value":"Bob"}, {"field":"gender", "value":"M"}]' as user_info
                         union all
                         select 2                                                                                                 as user_id,
                                '[{"field":"gender", "value":"F"}, {"field":"age", "value":22}, {"field":"name", "value":"Ana"}]' as user_info;

);

2 问题分析

问题的目标是构建一个K-V信息的新的map，从结果可以看出原始的映射数组中field,value这些字符串为无用信息。

2.1 方法1 特征法-通用解法

regexp_replace() + translate()函数

第一步：利用translate()函数删除[]：{""及空格这些多余符号

常常利用translate()函数来清洗字符串数据

select user_id
     , translate(user_info, '[{"" ]:', '') user_info
from sys_log;

注意此处保留右边的大括号}是为了 后面便于识别中间逗号, 的特征

 第二步：利用regexp_replace(）函数删除field,value字符串

select user_id
     , regexp_replace(translate(user_info, '[{"" ]:', ''), 'field|value', '') user_info
from sys_log;

第三步：将逗号替换为冒号：

select user_id
     , regexp_replace(regexp_replace(translate(user_info, '[{"" ]:', ''), 'field|value', ''), ',', ':') user_info
from sys_log;

第四步：将中间}：替换为逗号，

select user_id
     , regexp_replace(regexp_replace(regexp_replace(translate(user_info, '[{"" ]:', ''), 'field|value', ''), ',', ':'),
                      '}:', ',') user_info
from sys_log;

 

第五步：删掉结尾处的 }

select user_id
     , translate(
        regexp_replace(regexp_replace(regexp_replace(translate(user_info, '[{"" ]:', ''), 'field|value', ''), ',', ':'),
                       '}:', ','), '}', '') user_info
from sys_log;

 第六步：利用str_to_map函数组装成map结构

完整的SQL如下：

select user_id
     , str_to_map(translate(
        regexp_replace(regexp_replace(regexp_replace(translate(user_info, '[{"" ]:', ''), 'field|value', ''), ',', ':'),
                       '}:', ','), '}', '')) user_info
from sys_log;

 2.2 方法2枚举法（不通用）

当需要清洗的Key比较多时候，该方法由于要枚举，不通用，但此方法相对于方法1要好理解一些

我们直接给出答案

select user_id
     --组装成新map结构
     , str_to_map(concat('name:', name, nvl(concat(',', 'gender:', gender), ''),
                         nvl(concat(',', 'age:', age), ''))) as user_info
from (select user_id,
             max(case when user_info['field'] = 'name' then user_info['value'] end)   name,
             max(case when user_info['field'] = 'gender' then user_info['value'] end) gender,
             max(case when user_info['field'] = 'age' then user_info['value'] end)    age
      from (select user_id
                 -- 去掉{}“”及多余空格并将字符串转换为map结构
                 , translate(val, '{} ""', '')             trans_value
                 , str_to_map(translate(val, '{} ""', '')) user_info
            from (select user_id
                       , translate(user_info, '[]', '') user_info
                  from sys_log) t
                     --以}，{之间的逗号切割
                     lateral view outer explode(split(user_info, '(?<=\\}) *, *(?=\\{)')) tmp as val) t
      group by user_id) t

 

3 小结

本文给出了一种将将字符串数组清洗为简单map结构的解决方案，采用两种方法进行求解，其中方法1比较通用，当字符串数组中key值较多，依然适用，主要采用translate + regexp_replace函数进行数据的清洗和转换，这俩函数特别在字符串数据处理时特别有用。

 ~~END~~

  如果觉得本文对你有帮助，那么不妨也可以选择去看看我的博客专栏 ，部分内容如下：

数字化建设通关指南
专栏原价99，现在活动价29.9，按照阶梯式增长，直到恢复原价

主要内容：
（1）SQL进阶实战技巧
可以参考如下教程，具体链接如下

SQL很简单，可你却写不好？也许这才是SQL最好的教程

上面链接中的文章及技巧会不定期更新。

（2）数仓建模实战技巧和个人心得
       1）新人入职新公司后应如何快速了解业务？

       2）以业务视角看宽表化建设？

       3)  维度建模 or 关系型建模？

       4）业务模型与数据模型有什么区别？业务阶段的模型该如何建设？

       5）业务指标体系该如何建设？指标体系该如何维护？指标平台应如何建设？指标体系                           该由谁来搭建？

       6）如何优雅设计DWS层？DWS层模型好坏该如何评价？

       7）指标发生异常，该如何排查？应从哪些方面入手寻找问题点？

       8） 数据架构的选择，mpp or hadoop?

       9）数仓团队应如何体现自己的业务价值，讲好数据故事？

       10）BI与大数据有什么关系？BI与信息化、数字化之间有什么关系？BI与报表之间的关                          系？

       11）数据部门如何与业务部门沟通，并规划指引业务需求？

文章不限于以上内容，有新的想法也会及时更新到该专栏。

具体专栏链接如下：

数字化建设通关指南_莫叫石榴姐的博客-CSDN博客