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编写与实现

json内容:

取json的值:

select `date`,dev_seq AS devseq,
        json_extract(reading, '$.sharp' ) AS a,
        json_extract(reading, '$.peak' ) AS b,
        json_extract(reading, '$.flat' ) AS c,
        json_extract(reading, '$.valley' ) AS d,
        json_extract(reading, '$.total' ) AS f
        from device_daily_usage_electric

正常的json使用mysql 取数据如下即可

-- 查询记录
SELECT sname,JSON_EXTRACT(info,'$.age') FROM t_json;
SELECT sname,info->'$.age' FROM t_json;

当从数组重取数据时，需要添加数组下标， 

-- 查询记录
SELECT sname,JSON_EXTRACT(info,'$[0].age') FROM t_json;
SELECT sname,info->'$[0].age' FROM t_json;

 $[0]： 0代表数组下标 ，取第一条数据，注意，解析后的数据带双引号，可使用replace函数替换

json数组查询，用 JSON_CONTAINS(字段, JSON_OBJECT('json属性', "内容")) : [{}]查询这种形式的json数组

-- 查询记录
SELECT * FROM t_json  WHERE JSON_CONTAINS(info,JSON_OBJECT('age','18')) ;