get_json_object 函数
此函数为常用函数,可以取出json数据中的相应的字段的数值
-- get_json_object(json,'$.name') as name
select get_json_object('{"name":"zj","age":25}','$.name') as name ,get_json_object('{"name":"zj","age":25}','$.age') as age;
select get_json_object('{"name":"zj","age":25,"add":{"provinces":"河南","city":"周口"}}','$.add.city');
json_tuple 函数
select json_tuple('{"name":"zj","age":25}','name','age') ;
select b.c0 as name,b.c1 as age from (select json_tuple('{"name":"zj","age":25}','name','age'))b ;
Json 数组
-- 相当于UDTF “一进多出”
SELECT explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;'));
-- 说明:
-- 第一次替换:将'['或者']'替换为''
-- 第二次替换:将'},{' 替换为'};{'
-- 切割:按照';'进行切割
此方式也可以采用编写UDTF来实现,具体步骤可以查看Hive UDF GDF UDTF编写与实现