目录
tb_test表
create table `tb_test`
(
`user_id` bigint unsigned,
`login_info` json,
primary key (`user_id`)
) engine=innodb;
insert into `tb_test` values
(1, '{"tel": "13122335566", "QQ": "654321", "wechat": "jackfrued"}'),
(2, '{"tel": "13599876543", "weibo": "wangdachui123"}');
json_extract() and json_unquote()
通过json_extract函数,获取到了json对象的特定key的值
json_unquote 去除json字符串的引号，将值转成string类型
方式一：直接对json进行处理
select JSON_EXTRACT('{"tel": "13599876543", "weibo": "wangdachui123"}','$.tel'); # "13599876543"
select JSON_UNQUOTE(JSON_EXTRACT('{"tel": "13599876543", "weibo": "wangdachui123"}', '$.tel')); # 13599876543
方式二： 获取某个字段json对象特定key对应的所有值
select JSON_EXTRACT(`login_info`,'$.tel') from tb_test; # "13122335566" "13599876543"
select JSON_EXTRACT(tb_test.login_info, '$.tel') from tb_test; # "13122335566" "13599876543"
select json_unquote(json_extract(`login_info`, '$.tel')) from tb_test; # 13122335566 13599876543
实际运用：从json数据中提取相应数据
select
`user_id`,
json_unquote(json_extract(`login_info`, '$.tel')) as 手机号,
json_unquote(json_extract(`login_info`, '$.wechat')) as 微信
from `tb_test`;
tb_tags表，tb_users_tags表
建表
create table `tb_tags`
(
`tag_id` int unsigned not null comment '标签ID',
`tag_name` varchar(20) not null comment '标签名',
primary key (`tag_id`)
) engine=innodb;
insert into `tb_tags` (`tag_id`, `tag_name`)
values
(1, '70后'),
(2, '80后'),
(3, '90后'),
(4, '00后'),
(5, '爱运动'),
(6, '高学历'),
(7, '小资'),
(8, '有房'),
(9, '有车'),
(10, '爱看电影'),
(11, '爱网购'),
(12, '常点外卖');
create table `tb_users_tags`
(
`user_id` bigint unsigned not null comment '用户ID',
`user_tags` json not null comment '用户标签'
) engine=innodb;
insert into `tb_users_tags` values
(1, '[2, 6, 8, 10]'),
(2, '[3, 10, 12]'),
(3, '[3, 8, 9, 11]');
– 判断数组中有没有10这个标签
select user_id from tb_users_tags
where 10 member of (user_tags -> '$');
– 判断数组有无同时拥有2和10 两个标签
select user_id from tb_users_tags
where 10 member of (user_tags -> '$');
– 判断数组跟后面的数组有没有重叠的部分（交集）
select user_id from tb_users_tags
where json_overlaps(user_tags -> '$','[2,3,10]');
