(转)hive中解析json数组

2019独角兽企业重金招聘Python工程师标准>>>

hive中解析一般的json是很容易的，get_json_object就可以了。

但如果字段是json数组，比如

[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]，

直接调用get_json_object返回空值。这样的话对于不会写UDF的同学来说，解析json数组就变得很棘手，好在hive中自带了explode函数，从而让解析json数组变得有可能了。这里先介绍一下explode的使用方法。

explode(array)

select explode(array('A','B','C')) as col;
select tf.* from (select 0 from dual) t lateral view explode(array('A','B','C')) tf as col;
运行结果：
col 
C
B
A

函数说明：explode的参数是数组，提供了类似于列转的功能；假如参数数组长度为3，则返回的记录会是3行，且每列为各个数组项，如上。回到 
[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"}, 
 {"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]，
怎么解析出bssid?思路是通过explode把原数据变成2行数据
({"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"}和
 {"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}),

然后再使用get_json_object解析。
具体代码如下：
select ss.col
from (
      select 
      split(regexp_replace(regexp_extract(
                           '[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"}, 
                            {"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]',
                           '^\\[(.+)\\]$',1),
            '\\}\\,\\{', '\\}\\|\\|\\{'),
     '\\|\\|'
     ) as str
from dual) pp
lateral view explode(pp.str) ss as col ;

运行结果：
col 
{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}
{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"}

说明：因为原数据是string（并不是真正的数组类型）类型的，所以无法直接使用explode函数。
1.regexp_extract('xxx','^\\[(.+)\\]$',1) 这里是把需要解析的json数组去除左右中括号，需要注意的是这里的中括号需要两个转义字符\\[。
2.regexp_replace('xxx','\\}\\,\\{', '\\}\\|\\|\\{') 把json数组的逗号分隔符变成两根竖线||，可以自定义分隔符只要不在json数组项出现就可以。Note:odps中实操需要改成:regexp_replace('xxx','\\}\\,\\{', '}||{') 把json数组的逗号分隔符变成两根竖线||，可以自定义分隔符只要不在json数组项出现就可以。
3.使用split函数返回的数组，分隔符为上面定义好的。
4.lateral view explode处理3中返回的数组。
另外，hive中的json_tuple解析json比get_json_object更方便。

select ss.col,rr.appid,rr.ssid,rr.bssid
from (
      select split(regexp_replace(regexp_extract('
                        [{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"}, 
                        {"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]',
                        '^\\[(.+)\\]$',1),
             '\\}\\,\\{', '\\}\\|\\|\\{'),
       '\\|\\|'
     ) as str
from dual) pp
lateral view explode(pp.str) ss as col 
lateral view json_tuple(ss.col,'appid','ssid','bssid') rr as appid,ssid,bssid;

运行结果：
col appid ssid   bssid 
{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}10003and-BusinessAC:9C:E4:04:EE:52
{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"}\NMERCURY_05C46C:59:40:21:05:C4
json_tuple可以一次性解析多个字段，而get_json_object一次只能解析一个字段。

转载于:https://my.oschina.net/u/3204727/blog/1785044