Mysql中json类型数据查询

        mysql在5.7版本之后就开始支持json数据类型，并且mysql8.0版本对json的处理已经做的非常完善了。json数据类型的优点缺点可自己查询，本文主要介绍一些关于json数据类型的查询操作。

下面用这个表来执行查询演示:

CREATE TABLE `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `address` json NOT NULL COMMENT '住址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入几条数据

INSERT INTO `users` VALUES (1, '张三', '{"city": "石家庄市", "tags": ["家", "公司"], "district": "桥西区", "province": "河北省"}');
INSERT INTO `users` VALUES (2, '李四', '{"city": "广州市", "tags": ["宿舍"], "district": "珠海区", "province": "广州省"}');
INSERT INTO `users` VALUES (3, '王五', '{"city": "长春市", "district": "绿园区", "province": "吉林省"}');
INSERT INTO `users` VALUES (4, '刘六', '{"city": "昌平区", "province": "北京市"}');
INSERT INTO `users` VALUES (5, '张三三', '[{"city": "石家庄市", "tags": ["家", "公司", "学校"], "district": "桥西区", "province": "河北省"}, {"city": "郑州市", "tags": ["宿舍"], "district": "桥东区", "province": "河南省"}]');
INSERT INTO `users` VALUES (6, '李四四', '[{"city": "广州市", "tags": ["宿舍"], "district": "珠海区", "province": "广州省"}, {"city": "广州市", "district": "珠海区", "province": "广州省"}]');
INSERT INTO `users` VALUES (7, '王五六', '["家", "公司", "学校"]');

查询json对象指定属性值的数据

1、函数查询：json_extract(json字段, '$.json属性')

select * from users where json_extract(address, '$.province') = "河北省";

2、对象操作方法进行查询：json字段->'$.json属性'

select * from users where address->'$.province' = "河北省";

查询json数组指定下标值的数据

1、数组操作方式查询：字段->'$[0]'

select  * from users where address->'$[0]'= "家";

 根据JSON对象里面的属性个数查询

1、函数查询：json_length(json字段)

select * from users where json_length(address) = 2;

 根据JSON数组里面的数组长度查询

1、函数查询：json_length(json字段)

select * from users where json_length(address) = 2;

根据JSON对象属性值为数组的数组长度查询

1、函数查询：json_length(json字段, '$.json属性')

#获取addresss里面tags数组长度为2的数据
select * from users where json_length(address, '$.tags') = 2;

 查询JSON对象属性值为数组的任意项存在指定值查询

1、函数查询：JSON_CONTAINS(json字段,JSON_OBJECT('json数组属性', '内容'))

select * from users where JSON_CONTAINS(address,JSON_OBJECT('tags', '家'));

 查询JSON数组里面对象属性任意项存在指定属性的数据

select * from users where address->'$[*].city' is not null;

 查询JSON对象存在指定属性的数据

select * from users where address->'$.tags' is not null;