hive处理json数据_(转)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数组项出现就可以。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一次只能解析一个字段。