Hive的json解析

-- 数据准备
-- 假如有一张表test中有一个字段log，数据如下：
log:{"status":0,"startTime":"2021-03-31 14:40:19","endTime":"2021-03-31 14:40:32","totalTime":"12939s",}

get_json_object

--语法：
get_json_object(jsonString,'$.字段名')

--使用函数拿到每个字段的数据
select 
get_json_object(log,'$.status') as status,
get_json_object(log,'$.startTime') as startTime,
get_json_object(log,'$.endTime') as endTime,
get_json_object(log,'$.totalTime') as totalTime
from test;

-- 假设表的json字符串中又包含array数组，该怎么取数组中各个字段的值呢？假设log值如下：
{"status":true,"costtime",20s,"stuinfo":[{"id":"1","name":"Tim","sex":"male","age":"32"}]}
select 
get_json_object(log,'$.stuinfo') as stuinfo,
get_json_object(get_json_object(log,'$.stuinfo'),'$[0].id') as stu_id,
get_json_object(get_json_object(log,'$.stuinfo'),'$[0].name') as stu_name,
get_json_object(get_json_object(log,'$.stuinfo'),'$[0].sex') as stu_sex,
get_json_object(get_json_object(log,'$.stuinfo'),'$[0].age') as stu_age
from test;

:'
重要：$[0]代表的含义是整个数组中的内容
假如执行如下语句：
select get_json_object(get_json_object(log,'$.stuinfo'),'$[0]');
我们会得到如下的结果（代表数组的中括号不见了）：
{"id":"1","name":"Tim","sex":"male","age":"32"}
'

json_tuple

--语法(字段不加$.)：
json_tuple(jsonString,'字段1','字段2',...)

select 
b.status,
b.startTime,
b.endTime,
b.totalTime
from test a
lateral view json_tuple(log,'status','startTime','endTime','totalTime') b as status,startTime,endTime,totalTime;