mysql 处理 json数据

相关介绍：
链接1
链接2

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]');