HIVE 解析json和jsonarray

一、解析json对象

1、单层

select get_json_object(params，'$.key'）

示例：

select stu_id
,get_json_object(score,'$.corse_name') as corse_name
,get_json_object(score,'$.score') as score
from 
(
select 1 as stu_id,'{"corse_name":"c++","score":10}' as score
)a 

结果：

stu_id	corse_name	score
1	c++	10

2、嵌套多层

select  stu_id
       ,get_json_object(score,'$.family_info')    as family_info
       ,get_json_object(score,'$.family_info.ba') as family_info_ba
from
(
	select  1                                                                                                                                             as stu_id
	       ,'{"stu_id":"stu_id_1","corse_list":[{"corse_name":"c++","score":10},{"corse_name":"mysql","score":20}],"family_info":{"ba":"liu","ma":"li"}}' as score
)a

stu_id	family_info	family_info_ba
1	{"ba":"liu","ma":"li"}	liu

3、解析多个字段：

json_tuple,如果解析的key不存在则为null

select  stu_id
       ,corse_list
       ,family
       ,corse_name
from
(
	select  1                                                                                                                                             as stu_id
	       ,'{"stu_id":"stu_id_1","corse_list":[{"corse_name":"c++","score":10},{"corse_name":"mysql","score":20}],"family_info":{"ba":"liu","ma":"li"}}' as score
)a
lateral view json_tuple(score,'corse_list','family_info','corse_name') tmp as corse_list,family,corse_name

stu_id	corse_list	family	corse
1	[{"corse_name":"c++","score":10},{"corse_name":"mysql","score":20}]	{"ba":"liu","ma":"li"}	null

二、解析jsonarray 

1、利用brickhouse 里面的json_split ：将json格式的数组转成数组类型

--炸开json_array
select  stu_id
       ,get_json_object(score,'$.family_info')    as family_info
       ,get_json_object(score,'$.family_info.ba') as family_info_ba
       ,tmp 
from
(
	select  1                                                                                                                                             as stu_id
	       ,'{"stu_id":"stu_id_1","corse_list":[{"corse_name":"c++","score":10},{"corse_name":"mysql","score":20}],"family_info":{"ba":"liu","ma":"li"}}' as score
)a
lateral view explode(json_split(get_json_object(score,'$.corse_list'))) tmp as tmp

stu_id	family_info	family_info_ba	tmp
1	{"ba":"liu","ma":"li"}	liu	{"corse_name":"c++","score":10}
1	{"ba":"liu","ma":"li"}	liu	{"corse_name":"mysql","score":20}

2、利用get_json_object 

select  stu_id
       ,get_json_object(score,'$.corse_list[0]')    as corse_1
       ,get_json_object(score,'$.corse_list[1]') as corse_2
from
(
	select  1                                                                                                                                             as stu_id
	       ,'{"stu_id":"stu_id_1","corse_list":[{"corse_name":"c++","score":10},{"corse_name":"mysql","score":20}],"family_info":{"ba":"liu","ma":"li"}}' as score
)a