【Mysql】Mysql中json类型的属性取值

近期工作中遇到需要在mysql中json类型获取属性的一个需求，之前听过说这个类型，但并没有过多的关注。 结合网上的一些案例，于是做了一些归纳汇总。

主要用到的函数是：

JSON_EXTRACT(json字段列名,"$.json属性名")

JSON_UNQUOTE()：去除双引号

(1)初始化数据

CREATE TABLE student_info(
    id BIGINT NOT NULL   COMMENT '主键;' ,
    name VARCHAR(32) NOT NULL   COMMENT '姓名' ,
    address VARCHAR(128) NOT NULL   COMMENT '地址' ,
    expand json NOT NULL   COMMENT '扩展信息' ,
    PRIMARY KEY (id)
)  COMMENT = '学生信息表';

INSERT into student_info (id,name,address,expand) values(1,'小明','阿拉斯加','{"sex": "male","hobby": ["跑步","骑行"],"school": {"name": "清华大学","detail": [{"startDate": "2022-09-17 12:24:06","address":"北京五道口"}]}}');
INSERT into student_info (id,name,address,expand) values(2,'小草','夏威夷','{"sex": "male","hobby": ["遛狗","吸猫"],"school": {"name": "北京大学","detail": [{"startDate": "2022-09-17 12:24:06","address":"北京五棵松"}]}}');

(2) 取JSON的一级属性

select id,name,JSON_EXTRACT(expand,"$.sex") as 性别 from student_info;

去除双引号

select id,name,JSON_UNQUOTE(JSON_EXTRACT(expand,"$.sex")) as 性别 from student_info;

（3）下面依次是 取JSON中二级属性，及数组如何取值 就不一一截图，把SQL贴到下面

-- 2.取JSON 中的二级属性
select id,name,JSON_EXTRACT(JSON_EXTRACT(expand,"$.school"),"$.name") as 学校 from student_info;
-- 去除双引号
select id,name,JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(expand,"$.school"),"$.name")) as 学校 from student_info;

-- 3.取JSON中的一级属性（数组）
select id,name,JSON_EXTRACT(expand,"$.hobby") as 兴趣 from student_info;
--  取JSON中 数组值的第一个
select id,name,JSON_EXTRACT(expand,"$.hobby[0]") as 兴趣 from student_info;
--  去除双引号
select id,name,JSON_UNQUOTE(JSON_EXTRACT(expand,"$.hobby[0]")) as 兴趣 from student_info;

-- 4 取JSON 中的二级属性（数组）
select id,name,JSON_EXTRACT(JSON_EXTRACT(expand,"$.school"),"$.detail[0].address") as 地址 from student_info;
-- 去除双引号
select id,name,JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(expand,"$.school"),"$.detail[0].address")) as 地址 from student_info;

-- 5 作为查询条件时
select id,name from student_info where JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(expand,"$.school"),"$.detail[0].address")) = '北京五道口';

以上是在工作中实际用到的场景，可能还有其他更好的方法，待用到再及时补充吧！