hive中解析json数据(map数组型)

在hive中解析json数据，一般会想到get_json_object函数，当然json数据的复杂程度不一样，解析方法也会不一样，本文总结一下简单json和复杂json的解析过程。

1、简单json的解析
这里把只包含map(可以嵌套map)的json定义为简单json，这种数据比较容易解析，直接调用get_json_object函数就可以。

一个map情况：{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"}

select get_json_object('{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"}','$.bssid') as bssid 
from dual;

运行结果：
bssid
6C:59:40:21:05:C4

map嵌套的情况：{"person":"tom","food":{"fruit":"apple","meat":"pig"}}

select get_json_object('{"person":"tom","food":{"fruit":"apple","meat":"pig"}}','$.person') as person
,get_json_object('{"person":"tom","food":{"fruit":"apple","meat":"pig"}}','$.food.fruit') as fruit
from dual ;

运行结果：
person fruit
tom    apple

通过上面两个例子发现，对于类似这样简单json的解析，不管字段是在第一层map(第一个例子中的bssid)还是在嵌套的map(第二个例子中的fruit)里面，解析起来都挺简单。

2、复杂json的解析
这里主要讨论map数组的解析，比如[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]。
假如要解析bssid和ssid字段，调用get_json_object返回的是map数组，因为有多个数据项，无法直接解析出bssid和ssid。按照一般的逻辑，有多少数据项就产生多少条记录，相当于一条记录变成多条记录，这样的话解析起来就变得没那么简单，下面提供两种方法来处理这样的数据。

a.笛卡尔积方法
假如待解析表的字段(map数组型json)的数据项都是一样多的，也就是说数组的长度一样，比如长度都为2，数据样例[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]

set hive.mapred.mode=nostrict ; --打开笛卡尔积的开关
select
case when b.ind=0 then get_json_object(a.appinfo,'$[0].bssid')
else get_json_object(a.appinfo,'$[1].bssid') end bssid,
case when b.ind=0 then get_json_object(a.appinfo,'$[0].ssid')
else get_json_object(a.appinfo,'$[1].ssid') end ssid
from (
select '[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]' as appinfo
) a
join (select 0 as ind from dual union all
select 1 as ind from dual) b ;

运行结果：
bssid  ssid
6C:59:40:21:05:C4  MERCURY_05C4
AC:9C:E4:04:EE:52  and-Business

这种方法有两个缺点，其一map数组的长度很难保证一样长；其二数组长度大于10代码会很长，写起来麻烦也容易出错，所以有了下面的方法。
b.explode函数
hive中自带了explode函数，从而让解析map数组变得简单灵活，这里先介绍一下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
case when ss.col regexp '^\\{' and not ss.col regexp '\\}$' then concat(ss.col,'\}')
when not ss.col regexp '^\\{' and ss.col regexp '\\}$' then concat('\{',ss.col)
when ss.col regexp '^\\{' and ss.col regexp '\\}$' then ss.col
end appinfo
from (
select split(regexp_extract(a.appinfo,'^\\[(.+)\\]$',1),'\\}\\,\\{') as str
from
(
select '[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]' as appinfo
) a ) pp
lateral view explode(pp.str) ss as col ;

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

说明：因为原数据是string（并不是真正的数组类型）类型的，所以无法直接使用explode函数。
1.regexp_extract('xxx','^\\[(.+)\\]$',1) 这里是把需要解析的json数组去除左右中括号，需要注意的是这里的中括号需要两个转义字符\\[。
2.使用split函数拆分成数组，分隔符为'\\}\\,\\{'，其实就是},{。
3.lateral view explode处理2中返回的数组。
4.因为使用},{为分隔符，导致了拆分出来的数据可能头部少了{，或者尾部少了}，需要填充中括号。