MySQL Json格式的数据获取 小示例

在平时的学习或者工作有时会遇到 json 的数据字段，那我们如何从那个字段中获取我们想要的数据呢？

1、创建一个数据表

drop table if exists dina_test_table;
create table dina_test_table (id int ,employee json);

2、插入测试数据

insert into dina_test_table values(1,'{"name":"Dina","department":"数据组","Salary_obj":{"date":"2020-05-15","salary":"8000"}}');
insert into dina_test_table values(2,'{"name":"Augenstern","department":"测试组","Salary_obj":{"date":"2020-04-15","salary":"8500"}}');
insert into dina_test_table values(3,'{"name":"Yan","Salary_obj":{"date":"2020-05-15","salary":"8500"}}');

3、查看我们的数据

4、用 JSON_EXTRACT 来 提取我们想要的

select 
json_extract(employee,'$.name') name,
json_extract(employee,'$.department') department,
json_extract(employee,'$.Salary_obj.salary') salary
from dina_test_table;

5、查询 employee 字段 里面的数值（不带引号的）

select id,
employee ->> '$.name'  name,
employee ->> '$.department'  department,
employee ->> '$.Salary_obj.date'  date,
employee ->> '$.Salary_obj.salary' salary
from dina_test_table;