hive中解析json数组

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数组项出现就可以。
3.使用split函数返回的数组，分隔符为上面定义好的。
https://www.360docs.net/doc/6713870852.html,teral 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"} 10003 and-Business AC:9C:E4:04:EE:52
{"bssid":"6C:59:40:21:05:C4

","ssid":"MERCURY_05C4"} \N MERCURY_05C4 6C:59:40:21:05:C4

json_tuple可以一次性解析多个字段，而get_json_object一次只能解析一个字段。