Hive之解析Json数组_hive解析json数组

Hive自带的json解析函数

1、get_json_object函数

语法：get_json_object(json_string, ‘$.key’)

说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。这个函数每次只能返回一个数据项。

示例：

select 
get_json_object('{"name":"zhangsan","age":18}','$.name'),
--get_json_object('{"name":"zhangsan","age":18}','$.age'); 
name
zhangsan
1
2
3
4
5

2、json_tuple函数

语法：json_tuple(json_string, k1, k2 …)

说明：解析json的字符串json_string,可指定多个json数据中的key，返回对应的value。如果输入的json字符串无效，那么返回NULL。

示例：

with temp as (
  select 1 as str 
)
select 
b.name 
,b.age 
from  temp
lateral view  json_tuple('{"name":"zhangsan","age":18}','name','age') b as  name,age

name	age
zhangsan	18


select json_tuple('{"name":"zhangsan","age":18}','name','age')  as (name,age)

name	age
zhangsan	18


with temp as (
  select '{"name":"zhangsan","age":18}' as str 
)
select 
json_tuple(str,'name','age') as  (name,age)
from  temp

name	age
zhangsan	18


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

注意：上面的json_tuple函数中没有$.
总结：json_tuple相当于get_json_object的优势就是一次可以解析多个json字段。但是如果我们有个json数组，这两个函数都无法处理。

Hive解析json数组

如果有一个hive表，表中 json_str 字段的内容如下：

json_str
[{“website”:“baidu.com”,“name”:“百度”},{“website”:“google.com”,“name”:“谷歌”}]
我们想把这个字段解析出来，形成如下的结构：

website name
baidu.com 百度
google.com 谷歌

一、嵌套子查询解析json数组

hive>select json_tuple(json, 'website', 'name') 
from (
select explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\|\\{'),'\\|')) 
as json) t;
www.baidu.com   百度
google.com      谷歌

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

select 
get_json_object(json_str,'$.website') as website
,get_json_object(json_str,'$.name') as name
from  
(select 
regexp_replace(regexp_replace(str, '\\[|\\]',''),'\\}\\,\\{','\\}\\|\\{') as str
from  temp
) tt 
lateral view outer explode(split(str,'\\|'))  view_test  as json_str
www.baidu.com   百度
google.com      谷歌

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

二、使用 lateral view 解析json数组

hive表中 goods_id 和 json_str 字段的内容如下：

goods_id json_str
1,2,3 [{“source”:“7fresh”,“monthSales”:4900,“userCount”:1900,“score”:“9.9”},{“source”:“jd”,“monthSales”:2090,“userCount”:78981,“score”:“9.8”},{“source”:“jdmart”,“monthSales”:6987,“userCount”:1600,“score”:“9.0”}]
目的：把 goods_id 字段和 json_str 字段中的monthSales解析出来。

hive>select good_id,get_json_object(sale_json,'$.monthSales') as monthSales
from tableName 
LATERAL VIEW explode(split(goods_id,','))goods as good_id 
LATERAL VIEW explode(split(regexp_replace(regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\|\\{'),'\\|')) sales as sale_json;
goods_id	monthSales
1	4900
1	2090
1	6987
2	4900
2	2090
2	6987
3	4900
3	2090
3	6987

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

参考链接：https://mp.weixin.qq.com/s?src=11×tamp=1645843496&ver=3643&signature=EVVwSECvkU–LNu4TDblVU9naGgiO2s45-xXxNP-8uaKXh2lJVyL7pbMjfx0nnweeynmozDA-6WeYEZvLTb8Sl6LVFwTnUTGp4gsVvRoEhT4AHA43bXMpqrjfnOGTL&new=1