mysql json类型数据查询

创建基础数据表格

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"}}