MySQL——处理JSON类型的数据

- MySQL对JSON类型数据的处理

参考视频：快速学习MySQL 8 JSON

注意，本文的键名也可以叫key，键值也可以叫value，意思是一样的

1. 字符串查询：JSON_EXTRACT

假设我们有一个表叫做testDemo，其中有一个字段叫做details，类型为JSON，他的数据结构如下，我们就以这个结构为例（这是个例子，假设有很多行数据，他们的detail都是这样的结构，键名相同，但是键值不同）：

{
	cpu: "A100",
	system: "Windows10",
	ram: "8G",
	disk: "500G,SSD"
}

描述：按要求在JSON字符串种提取某个键名的值（主要用于键值对的JSON数据），接受两个参数：JSON_EXTRACT(目标字段名, 匹配的键名)
①（Map）获取JSON内指定键名的键值，并且把查询到的字段值拼接在表后：

-- 写法一
SELECT *, JSON_EXTRACT(detail, '$.cpu') AS CPU FROM testDemo;
-- 从testDemo中获取字段为detail的字符串数据，并且解析其中键名为cpu的数据，拼接在完整查询表的后面，AS是指定查询拼接的这个字段的名字
-- 写法二
SELECT detail->'$.cpu' FROM testDemo;
-- 这样查询只会显示cpu字段的值，也就是不会拼接在数据表后，而是单独一个字段

拼接的样子如下，如果不拼接，就是只有CPU这一个字段的他下面的值：

这里表的本身只有id，name，price，colors，details四个字段，第五个字段CPU是查询后拼接在查询表的最后，而不是表原本有的字段。
不拼接是这样：

如果你需要纯粹的字符串（也就是不要双引号，可以这样写）

SELECT JSON_UNQUOTE(detail->'$.cpu') FROM testDemo;
-- 或者这样写
SELECT detail->>'$.cpu' FROM testDemo;
-- 同理①如果按下面这样写，那么最后的cpu字段的结果也不会有双引号
SELECT *, detail->>'$.cpu' FROM testDemo;

结果就是：

②（Map）获取JSON内指定键名的符合条件的键值，并且把查询到的字段值拼接在表后：

SELECT *, JSON_EXTRACT(detail, '$.cpu') AS CPU FROM testDemo WHERE JSON_EXTRACT(detail, '$.cpu') = 'A100';
-- 同①类似，不过这里要求获取的是键名为cpu键值为A100的数据，同样会拼接到表后

③（Map）获取JSON内的数组：

第一个是获取键名为c的全部元素，，输出是：[3,4,5]
第二个是获取键名为c，索引为1和2的元素（数组索引从0开始），输出是：[4,5]
第三个是获取键名为c，索引为倒数第二个的元素，输出为：4

2. 数组查询：MEMBER OF

SELECT * FROM testDemo WHERE 'white' MEMBER OF (color);
-- 查询testDemo表所有字段，条件是字段color（JSON类型，存储了数组）JSON数组中有white这个元素的存在

3. 键值对筛选：JSON_CONTAINS(使用JSON_OBJECT组成键值对形式条件)

SELECT * FROM testDemo WHERE JSON_CONTAINS(details, JSON_OBJECT('system', 'iOS'));
-- 查询testDemo表全部字段，条件是details（JSON类型）字段，键名为system，键值为iOS的数据

4. 判断是否存在某个键名：JSON_CONTAINS_PATH

SELECT * FROM testDemo WHERE JSON_CONTAINS_PATH(details, 'one|all', '$.cpu');
-- 查询testDemo表全部字段，条件是details（JSON类型）字段，中含有一个|全部包含，键名为cpu
-- 当然你可以写满足多条，选用one就是有一个满足就返回，all就是要全部满足才返回，例如：
SELECT * FROM testDemo WHERE JSON_CONTAINS_PATH(details, 'one|all', '$.cpu', '$.ram', '$.disk');

5. 按键值查询键名：JSON_SEARCH

SELECT details, JSON_SEARCH(details, "one|all", 'Kirin 980') FROM testDemo;
-- 查询 testDemo表中，details（JSON类型）字段的字符串，满足一个/全部满足，键值为Kirin 980的数据，只展示details列，把查询结果拼接在这个列后，没有就显示null，使用all的话，返回结果是一个数组类型

6. 修改JSON数据：JSON_SET/JSON_INSERT/JSON_REPLACE/JSON_REMOVE

让我们一个一个看：
1.首先SET是设置了一个JSON数组字符串（数组第一个元素索引是0），用于后续的操作
2.JSON_SET，用于修改/添加元素（有就是覆盖修改，没有就是新增）
例如此处：
他选择了我们设置好的JSON字符串@j，对第二个元素（也就是b那个键值对）的值（数组）的第1个元素（索引为0）修改为1【因为已经存在，就修改它】；
再对@j的第三个元素（数组）的第三个元素修改为2【不存在，增加它】；
结果输出为：
3.JSON_INSERT，插入数据，没有就新增，有就跳过
例如此处：
对@j的第二个元素的键名为b的值的，第一个元素，插入1【由于已经存在值，跳过插入】；
再对@j的第三个元素，的第三个元素插入2；
结果输出为：
4.JSON_REPLACE，替换，有才替换，没有就不替换
例如此处：
对@j的第二个元素的键名为b的值的，第一个元素，替换为1；
再对@j的第三个元素，的第三个元素替换为2【由于不存在第三个元素，因此跳过替换】；
结果输出为：
5.JSON_REMOVE，删除，数据不存在会被跳过删除
对@j的第三个元素进行删除（数组）；再对第二个元素键名为b的第二个元素删除（删除false）；再次对第二个元素键名为b的第二个元素删除【由于已经不存在，跳过删除】；
结果输出为：

7. 聚合查询：JSON_ARRAYAGG（类似于GROUP_CONCAT）

我们以这个表为例：

我们想分组查询出价格【主】和产品名字（价格只展示一次，但是可以有不同的名字）

SELECT GROUP_CONCAT(name), price FROM testDemo GROUP BY price;
SELECT JSON_ARRAYAGG(name), price FROM testDemo GROUP BY price;

第一行效果是：
第二行是：
区别在于第一个是直接打印值，第二个是输出成一个JSON字符串。

8. 获取一个JSON键值对字符串的全部键名：JSON_KEYS

我们以这个表为例：

要获取details的JSON键值对字符串的全部键值：

SELECT JSON_KEYS(details) FROM testDemo;

输出为：

9. 获取一个JSON键值对字符串有多少个键名：JSON_LENGTH

SELECT JSON_LENGTH(details) FROM testDemo;

输出：

10. 验证一个JSON是否合法：JSON_VALID

SELECT JSON_VALID(你的json字符串);
-- 合法输出1，否则输出0

11. 格式化JSON：JSON_PRETTY

SELECT JSON_PRETTY(你的json字符串);
-- 例如：
SELECT JSON_PRETTY({["cpu":"骁龙865","system":"android","storage":"128G"},
{"cpu": "A8", "system" : "ios", "storage" :"256G"}]');

输出结果为：

12. 纵向改横向（输出类似于表格的形式）：JSON_TABLE

SELECT * FROM JSON_TABLE(
	'[{"cpu":"骁龙865","system" :"android","storage":"128G"},
	{"cpu": "A8", "system" :"ios", "storage": "256G"}]",
		"$[*]" COLUMNS(
			CPU VARCHAR(100) PATH "$.cpu",
			`System` VARCHAR(100) PATH "$.system",
			`Storage` VARCHAR(100) PATH "$.storage"
		)
) AS JT;
-- 此处由于system和storage是MYSQL的保留关键字，因此使用``进行转义

输出结果是：

13. 自定义JSON数据验证：JSON_SCHEMA_VALIDATION_REPORT

用法：

SELECT JSON_SCHEMA_VALIDATION_REPORT(自定义的校验规则，要被校验的JSON字符串)

如果通关，会返回{"valid": true}
否则返回：{"valid": false, "reason": "原因", "shema-location": "相关的自定义校验规则出现失败的位置",
"document-location": "被校验的JSON字符串出现失败的位置","shema-failed-keyword": "导致出现失败的条件"}
例子：

SET @schema = '{
	"id": "http://json-schema.org/geo",
	"$schema": "http://json-schema.org/draft-04/schema#",
	"description": "A geographical coordinate",
	"type": "object",
	"properties": {
		"latitude": {
			"type": "number",
			"minimum": -90,
			"maximum": 90
		},
		"longitude": {
			"type": "number",
			"minimum": -180,
			"maximum": 180
		}
	},
	"required": ["latitude", "longitude"]
}';
-- properties内规定了校验规则，接受指定两个键名，值要求为数字类型，且有不同的取值范围，required指定哪些键名是必填的

SET @document = '{
	"latitude": 63.444697,
	"longitude": 110.445118.
}'
SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)

最终结果输出：

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 函数的参数类型
• SQL Server 检测是不是数字型数据的两种方法
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 和利时MACS6通过dat文件实现任意时间范围与测点的历史数据提取与科研分析
• 深入理解spring框架：剖析多线程模式下数据库连接
• 数据可视化是如何帮助大型企业提高效率的？
• redis 数据库
• openpyxl模块--------------------------------重复数据
• Django客户端应用1向服务端应用2发送POST请求并接收解析数据

类型 数据 MySQL JSON字段mysql-json类型mysql mybatisplus类型mysql json 类型 数据mysql json 数据mysql json 类型 数据mysql 金钱 类型 数据mysql 类型 数据mysql 8.0 常用 类型 数据mysql 类型 数据bigint mysql 类型 引擎 数据mysql