SQL之一天一个小技巧：如何使用HQL提取JSON中 key值

目录

0 问题描述

1 问题解决

2 小结

---

0 问题描述

如下json_str

json_str
[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]

 我想获取该json中的每一个key值，如何做？

1 问题解决

（1）先将json_str中的[]及{}去掉

利用translate()函数处理

select translate('[{"website":"baidu.com","name":"百度"} ,{"website":"google.com","name":"谷歌"}]'
                  ,'[]{}""','') 

结果如下：

OK
website:baidu.com,name:百度,website:google.com,name:谷歌
Time taken: 1.335 seconds, Fetched: 1 row(s)

（2）将逗号替换成冒号:

select regexp_replace(translate('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]'
                  ,'[]{}""','') ,'\,','\:')

    计算结果如下：

OK
website:baidu.com:name:百度:website:google.com:name:谷歌
Time taken: 0.165 seconds, Fetched: 1 row(s)

 （3）将步骤2计算的结果用posexplode()函数展开，获取索引值及具体值

select pos+1 as rn
      ,val
from(
    select regexp_replace(translate('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]'
                      ,'[]{}""','') ,'\,','\:') as str
) t1 lateral view posexplode(split(str,':')) t2 as pos,val 

计算结果如下：

OK
1	website
2	baidu.com
3	name
4	百度
5	website
6	google.com
7	name
8	谷歌
Time taken: 0.224 seconds, Fetched: 8 row(s)

 （4）由于K-V是一组对偶元组，因此K为奇数行，我们只需要取出奇数行的记录即可

select rn
      ,val as key
from(
    select pos+1 as rn
          ,val
    from(
        select regexp_replace(translate('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]'
                          ,'[]{}""','') ,'\,','\:') as str
    ) t1 lateral view posexplode(split(str,':')) t2 as pos,val
) m 
where rn%2=1 

计算结果如下：

OK
1	website
3	name
5	website
7	name
Time taken: 0.294 seconds, Fetched: 4 row(s)

2 小结

本文给出了一种通过HQL提取JSON中 key值的方法和技巧，主要使用的知识点如下：

• （1）字符替换函数：translate()函数
• （2）字符串替换函数：regexp_replace()函数
•   (3) 列转行：lateral view posexplode()函数
• （4）获取奇数行记录：mod(rn,2)=1

欢迎关注石榴姐公众号"我的SQL呀"，关注我不迷路