创建基础数据表格
CREATE TABLE `t_test_json` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`content` json DEFAULT NULL COMMENT '内容',
`createtime` datetime DEFAULT NULL COMMENT '时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
复制代码
插入测试数据INSERT INTO t_test_json (id, content, createtime) value (1, '{"username":"joker","age":10,"birthday":"2019-02-03 12:12:12","address":{"country":"china","province":{"city":"dalian"}}}',NOW())
查询json深度
select json_depth(content) from t_test_json;
result : 4
查询json类型
select json_type(content) from t_test_json;
resutlt : OBJECT
查询包含的keys $ 表示根路径
select json_keys(content) from t_test_json;
result : ["age", "address", "birthday", "username"]select json_keys(content,'$') from t_test_json;
result : ["age", "address", "birthday", "username"]select json_keys(content,'$.address') from t_test_json;
result : ["country", "province"]
查询key的内容 $ 表示根路径
select json_extract(content,'$') from t_test_json;
result : {"age": 10, "address": {"country": "china", "province": {"city": "dalian"}}, "birthday": "2019-02-03 12:12:12", "username": "joker"}select json_extract(content,'$.address') from t_test_json;
result : {"country": "china", "province": {"city": "dalian"}}