昨天说了，今天单独拿一篇出来写一下JSON格式的读取。在很久很久以前，MySQL是没有JSON格式的，那个时候还很羡慕非关系型数据库，它们可以自由存储各种数据。MySQL从5.7版本开始就支持JSON格式的，从此也可以对外号称关系型数据和非关系型数据都通吃了。但是最开始对JSON格式数据的优化不是很优化，效率有点低。但这个问题他们自己也肯定知道的，先有再优嘛，于是8.0后MySQL对JSON格式的优化已经很好了，现在使用起来如果数据量不是特别特别大，使用效果还是很好的。

下面就主要介绍一下JSON格式的创建、插入、读取和修改，基本如果不是开发人员、数据库管理人员，作为一个数据分析师应该是够用了。

【创建】

可以使用SQL语句创建，如下，最后就创建了一个带JSON格式的表格↓

CREATE TABLE `salary` (  `姓名` varchar(6) NOT NULL,  `月份` varchar(3) NOT NULL,  `工资` decimal(12,2) DEFAULT NULL,  `个税` decimal(12,2) DEFAULT NULL,  `其他费用` json DEFAULT NULL,  PRIMARY KEY (`姓名`,`月份`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

当然也是可以通过Navicat直接创建就行了↓

【插入数据】

然后使用常规的INSERT INTO插入数据就行了，需要注意的是JSON格式使用键值对组成的，也可以内部嵌套数据，我们这里插入7条数据，SQL语句如下↓

INSERT INTO salary VALUES("李华","7月",2000,11,'{"chailv":333,"gaowen":222,"xiayu":11,"zhaodai":1200}');INSERT INTO salary VALUES("李华","8月",2200,11,'{"chailv":335,"gaowen":225,"xiayu":45}');INSERT INTO salary VALUES("韩梅梅","7月",4400,33,'{"chailv":158,"gaowen":222}');INSERT INTO salary VALUES("韩梅梅","8月",5500,66,'{"chailv":456,"gaowen":222,"zhaodai":5000}');INSERT INTO salary VALUES("张三三","7月",2000,11,'{"chailv":100,"gaowen":222}');INSERT INTO salary VALUES("张三三","8月",3000,14,'{"chailv":200,"gaowen":222}');INSERT INTO salary VALUES("张三三","10月",4000,51,'{"chailv":300,"gaowen":222,"qita":{"q1":111,"q2":222}}');

除了使用{}插入JSON格式数据，还可以使用JSON_OBJECT()函数转换成JSON格式的数据，下面再插入一条数据，SQL语句如下↓

INSERT INTO salary VALUES("李华","9月",2454,15,JSON_OBJECT("chailv",999,"zhaodai",1111));

【读取数据】

一般情况下数据分析就用这个查询功能就行了。json_extract()函数：从json中返回想要的字段，用法是，json_extract(字段名,$.json字段名)。也可以使用 json字段名->'$.json属性'进行查询，下面分别有两种查询方式进行演示，把其他费用里面的各项费用单独查询出来。

SELECT 姓名,月份,工资,个税, JSON_EXTRACT(其他费用, '$.chailv') AS 差旅费, JSON_EXTRACT(其他费用, '$.gaowen') AS 高温费, 其他费用->'$.xiayu' AS 下雨费, 其他费用->'$.zhaodai' AS 招待费FROM salary

JSON里面的字段也是可以作为条件进行查询的，比如我们要查询差旅费大于300的人员和月份，SQL预计如下↓

SELECT 姓名,月份,工资,个税, JSON_EXTRACT(其他费用, '$.chailv') AS 差旅费FROM salaryWHERE JSON_EXTRACT(其他费用, '$.chailv')>300

如果遇到JSON嵌套的情况，也是可以用两层JSON_EXTRACT函数进行查询的，比如张三三10月其他费用里面有个q1和q2两个费用，现在需要单独查询出来，SQL语句如下↓

SELECT 姓名,月份,工资,个税, JSON_EXTRACT(JSON_EXTRACT(其他费用, '$.qita'),'$.q1') AS Q1费用, JSON_EXTRACT(JSON_EXTRACT(其他费用, '$.qita'),'$.q2') AS Q2费用FROM salary

JSON_KEYS函数可以查询出JSON字段里面的键名字，SQL语句如下↓

SELECT 姓名,月份,工资,个税, JSON_KEYS(`其他费用`)FROM salary

【更新数据】

UPDATE  salary SET  `其他费用` = JSON_SET( `其他费用`, "$.chailv", 10000, "$.gaowen", 2000 ) WHERE 姓名 = "韩梅梅" AND `月份` = "8月"

End