MySQL之json数据操作
目录
- 1 MySQL之JSON数据
- 1.1 建表添加数据
- 1.2 基础查询操作
- 1.3 JSON函数操作
- 1.3.1 官方json函数
- 1.3.2 ->、->>区别
- 1.3.3 json_extract():从json中返回想要的字段
- 1.3.4 JSON_CONTAINS():JSON格式数据是否在字段中包含特定对象
- 1.3.5 JSON_OBJECT():将一个键值对列表转换成json对象
- 1.3.6 JSON_ARRAY():创建JSON数组
- 1.3.7 JSON_TYPE():查询某个json字段属性类型
- 1.3.8 JSON_KEYS():JSON文档中的键数组
- 1.3.9 JSON_SET():将数据插入JSON格式中，有key则替换，无key则新增
- 1.3.10 JSON_INSERT():插入值（往json中插入新值，但不替换已经存在的旧值）
- 1.3.11 JSON_REPLACE()
- 1.3.12 JSON_REMOVE():从JSON文档中删除数据
1 MySQL之JSON数据
总所周知，mysql5.7以上提供了一种新的字段格式json，大概是mysql想把非关系型和关系型数据库一口通吃，所以推出了这种非常好用的格式，这样，我们的很多基于mongoDB的业务都可以用mysql去实现了。当然了，5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0解决了。今天我们就针对mysql的json数据格式操作做一个简单的介绍
1.1 建表添加数据
这里我们先创建一个简单的含json格式的数据库表，其中json_value就为json格式的字段。
CREATE TABLE `dept` (
`id` int(11) NOT NULL,
`dept` varchar(255) DEFAULT NULL,
`json_value` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
添加数据
insert into dept VALUES(1,'部门1','{"deptName": "部门1", "deptId": "1", "deptLeaderId": "3"}');
insert into dept VALUES(2,'部门2','{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}');
insert into dept VALUES(3,'部门3','{"deptName": "部门3", "deptId": "3", "deptLeaderId": "5"}');
insert into dept VALUES(4,'部门4','{"deptName": "部门4", "deptId": "4", "deptLeaderId": "5"}');
insert into dept VALUES(5,'部门5','{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}');
1.2 基础查询操作
用法提示：
- 如果
json字符串不是数组，则直接使用$.字段名 - 如果
json字符串是数组[Array]，则直接使用$[对应元素的索引id]
1.2.1 一般json查询
使用 json字段名->'$.json属性' 进行查询条件
举个例子：如果想查询deptLeader=张五的数据，那么sql语句如下：
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';
查询出来的结果如下：
1.2.2 多个条件查询
比如想查dept为“部门3”和deptLeaderId=5的数据，sql如下：
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and dept='部门3';
查询和关系型数据库查询一致。
1.2.3 json中多个字段关系查询
比如想查询json格式中deptLeader=张五和deptId=5的数据
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';
1.2.4 关联表查询
这里我们再创建一张包含json格式的表
CREATE TABLE `dept_leader` (
`id` int(11) NOT NULL,
`leaderName` varchar(255) DEFAULT NULL,
`json_value` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入一些测试数据
insert into dept_leader VALUES(1,'leader1','{"name": "王一", "id": "1", "leaderId": "1"}');
insert into dept_leader VALUES(2,'leader2','{"name": "王二", "id": "2", "leaderId": "3"}');
insert into dept_leader VALUES(3,'leader3','{"name": "王三", "id": "3", "leaderId": "4"}');
insert into dept_leader VALUES(4,'leader4','{"name": "王四", "id": "4", "leaderId": "5"}');
insert into dept_leader VALUES(5,'leader5','{"name": "王五", "id": "5", "leaderId": "5"}');
这里我们要连表查询在dept 表中部门leader在dept_leader 中的详情
SELECT * from dept,dept_leader
WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;
1.3 JSON函数操作
写到这里大家都发现了，我们查询的json都是整条json数据，这样看起来不是很方便，那么如果我们只想看json中的某个字段怎么办？
1.3.1 官方json函数
| Name | Description | 解释 |
|---|---|---|
| -> | Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT() | 计算路径后返回JSON列的值;相当于JSON_EXTRACT () |
| ->> | Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()). | 从JSON列返回值后，就算路径和取消引号的结果;相当于JSON_UNQUOTE (JSON_EXTRACT ()) |
| JSON_ARRAY() | Create JSON array | 创建JSON数组 |
| JSON_ARRAY_APPEND() | Append data to JSON document | 向JSON文档追加数据 |
| JSON_ARRAY_INSERT() | Insert into JSON array | 插入JSON数组 |
| JSON_CONTAINS() | Whether JSON document contains specific object at path | JSON文档是否包含路径上的特定对象 |
| JSON_CONTAINS_PATH() | Whether JSON document contains any data at path | JSON文档是否在路径上包含任何数据 |
| JSON_DEPTH() | Maximum depth of JSON document | JSON文档的最大深度 |
| JSON_EXTRACT() | Return data from JSON document | 从JSON文档返回数据 |
| JSON_INSERT() | Insert data into JSON document | 将数据插入JSON文档 |
| JSON_KEYS() | Array of keys from JSON document | 来自JSON文档的键数组 |
| JSON_LENGTH() | Number of elements in JSON document | JSON文档中的元素数量 |
| JSON_MERGE_PATCH() | Merge JSON documents, replacing values of duplicate keys | 合并JSON文档，替换重复键的值 |
| JSON_MERGE_PRESERVE() | Merge JSON documents, preserving duplicate keys | 合并JSON文档，保留重复的密钥 |
| JSON_OBJECT() | Create JSON object | 创建JSON对象 |
| JSON_OVERLAPS() | Compares two JSON documents, returns TRUE (1) if these have any key-value pairs or array elements in common, otherwise FALSE (0) | 比较两个JSON文档，如果它们有共同的键值对或数组元素，则返回TRUE(1)，否则返回FALSE (0) |
| JSON_PRETTY() | Print a JSON document in human-readable format | 以人类可读的格式打印JSON文档 |
| JSON_QUOTE() | Quote JSON document | 引用JSON文档 |
| JSON_REMOVE() | Remove data from JSON document | 从JSON文档中删除数据 |
| JSON_REPLACE() | Replace values in JSON document | 替换JSON文档中的值 |
| JSON_SCHEMA_VALID() | Validate JSON document against JSON schema; returns TRUE/1 if document validates against schema, or FALSE/0 if it does not | 针对JSON模式验证JSON文档;如果文档针对模式进行验证，则返回TRUE/1，否则返回FALSE/0 |
| JSON_SCHEMA_VALIDATION_REPORT() | Validate JSON document against JSON schema; returns report in JSON format on outcome on validation including success or failure and reasons for failure | 针对JSON模式验证JSON文档;以JSON格式返回关于验证结果的报告，包括成功或失败以及失败原因 |
| JSON_SEARCH() | Path to value within JSON document | JSON文档中值的路径 |
| JSON_SET() | Insert data into JSON document | 将数据插入JSON文档 |
| JSON_STORAGE_FREE() | Freed space within binary representation of JSON column value following partial update | 在部分更新后释放JSON列值的二进制表示形式中的空间 |
| JSON_STORAGE_SIZE() | pace used for storage of binary representation of a JSON document | 用于存储JSON文档的二进制表示的空间 |
| JSON_TABLE() | Return data from a JSON expression as a relational table | 以关系表的形式从JSON表达式返回数据 |
| JSON_TYPE() | Type of JSON value | JSON值类型 |
| JSON_UNQUOTE() | Unquote JSON value | 不引用JSON值 |
| JSON_VALID() | Whether JSON value is valid | JSON值是否有效 |
| JSON_VALUE() | Extract value from JSON document at location pointed to by path provided; return this value as VARCHAR(512) or specified type | 根据所提供的路径从JSON文档中所指向的位置提取值;返回该值为VARCHAR(512)或指定的类型 |
| MEMBER OF() | Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0) | 如果第一个操作数匹配作为第二个操作数的JSON数组中的任何元素，则返回true(1)，否则返回false (0) |
1.3.2 ->、->>区别
->会保持json文档格式中原来格式，但->>会把所有引号去掉
1.3.2.1 在field中使用
->在field中使用的时候结果带引号，->>的结果不带引号
select json_value->'$.deptId' from dept
select json_value->>'$.deptId' from dept
1.3.2.2 在where条件中使用
特别注意：->当做where查询是要注意类型的，->>是不用注意类型的
select * from dept where json_value->'$.deptId'=1
select * from dept where json_value->'$.deptId'='1'
select * from dept where json_value->>'$.deptId'=1
select * from dept where json_value->>'$.deptId'='1'
1.3.2.3 在order中使用
没有发现有什么区别
select * from dept order by json_value->'$.deptId'
select * from dept order by json_value->>'$.deptId'
1.3.3 json_extract():从json中返回想要的字段
用法：json_extract(字段名,$.json字段名)
事例：
select id,json_extract(json_value,'$.deptName') as deptName from dept;
1.3.4 JSON_CONTAINS():JSON格式数据是否在字段中包含特定对象
用法: JSON_CONTAINS(target, candidate[, path])
事例:如果我们想查询包含deptName=部门5的对象
select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))
1.3.5 JSON_OBJECT():将一个键值对列表转换成json对象
比如我们想查询某个对象里面的值等于多少
比如我们添加这么一组数据到dept表中：
insert into dept VALUES(6,'部门9','{"deptName": {"dept":"de","depp":"dd"}, "deptId": "5", "deptLeaderId": "5"}');
我们可以看到deptName中还有一个对象，里面还有dept和depp两个属性字段，那么我们应该怎么查询depp=dd的员工呢。
用法：JSON_OBJECT([key, val[, key, val] …])
事例：
SELECT * from (
SELECT *,json_value->'$.deptName' as deptName FROM dept
) t WHERE JSON_CONTAINS(deptName,JSON_OBJECT("depp","dd"));
1.3.6 JSON_ARRAY():创建JSON数组
比如我们添加这么一组数据到dept表中：
insert into dept VALUES(7,'部门9','{"deptName": ["1","2","3"], "deptId": "5", "deptLeaderId": "5"}');
insert into dept VALUES(7,'部门9','{"deptName": ["5","6","7"], "deptId": "5", "deptLeaderId": "5"}');
用法：JSON_ARRAY([val[, val] …])
事例：我们要查询deptName包含1的数据
SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1"))
1.3.7 JSON_TYPE():查询某个json字段属性类型
用法：JSON_TYPE(json_val)
事例：比如我们想查询deptName的字段属性是什么
SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept
1.3.8 JSON_KEYS():JSON文档中的键数组
用法：JSON_KEYS(json_value)
事例：比如我们想查询json格式数据中的所有key
SELECT JSON_KEYS(json_value) FROM dept
接下来的3种函数都是新增数据类型的：
JSON_SET(json_doc, path, val[, path, val] …)
JSON_INSERT(json_doc, path, val[, path, val] …)
JSON_REPLACE(json_doc, path, val[, path, val] …)
1.3.9 JSON_SET():将数据插入JSON格式中，有key则替换，无key则新增
这也是我们开发过程中经常会用到的一个函数
用法：JSON_SET(json_doc, path, val[, path, val] …)
事例：比如我们想针对id=2的数据新增一组：newData:新增的数据,修改deptName为新增的部门1
sql语句如下：
update dept set json_value=JSON_SET('{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2;
注意：json_doc如果不带这个单元格之前的值，之前的值是会新值被覆盖的，比如我们如果更新的语句换成:
update dept set json_value=JSON_SET('{"a":"1","b":"2"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2
我们可以看到这里json_doc是{“a”:“1”,“b”:“2”}，这样的话会把之前的单元格值覆盖后再新增/覆盖这个单元格字段
1.3.10 JSON_INSERT():插入值（往json中插入新值，但不替换已经存在的旧值）
用法：JSON_INSERT(json_doc, path, val[, path, val] …)
事例：
UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部门2','$.newData2','新增的数据2')
WHERE id=2
我们可以看到由于json_doc变化将之前的值覆盖了，新增了deptName和newData2.
如果我们再执行以下刚才的那个sql，只是换了value，我们会看到里面的key值不会发生变化。
因为这个函数只负责往json中插入新值，但不替换已经存在的旧值。
1.3.11 JSON_REPLACE()
用法：JSON_REPLACE(json_doc, path, val[, path, val] …)
用例：
如果我们要更新id=2数据中newData2的值为：更新的数据2
sql语句如下：
UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}', '$.newData2', '更新的数据2') WHERE id =2;
1.3.12 JSON_REMOVE():从JSON文档中删除数据
用法：JSON_REMOVE(json_doc, path[, path] …)
举例：删除key为a的字段。
UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "更新的数据2"}','$.a') WHERE id =2;
