MySQL对JSON的正确操作方法

我的身前一尺是我的世界

于 2021-08-17 18:17:27 发布

阅读量831

点赞数 1

分类专栏： MySQL 文章标签： mysql操作json json函数

本文链接：https://blog.csdn.net/qq_39706570/article/details/119748140

版权

MySQL 专栏收录该内容

21 篇文章 3 订阅

订阅专栏

版本

version 8.0

目标

熟悉常用的JSON函数，并合理使用之。

创建数据

/*创建表*/
CREATE TABLE `json_test` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键',
  `student_to_book` TEXT COMMENT '学生租书数据',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*创建成绩表。*/
CREATE TABLE `course_score` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `courseId` int DEFAULT NULL COMMENT '课程主键',
  `studentId` int DEFAULT NULL COMMENT '学生主键',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `score` float(4,1) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生成绩表';

INSERT INTO `course_score` (`id`, `courseId`, `studentId`, `create_time`, `score`)
VALUES (1, 100001, 200001, '2021-06-10 11:22:41', 99.5),
	(2, 100002, 200001, '2021-06-04 11:23:02', 115.5),
	(3, 100003, 200001, '2021-06-10 11:23:23', 150.0),
	(4, 100001, 200002, '2021-06-01 11:23:41', 85.0),
	(5, 100002, 200002, '2021-06-10 11:23:51', 140.0),
	(6, 100003, 200002, '2021-06-10 11:24:01', 130.0),
	(7, 100001, 200003, '2021-06-10 11:24:19', 120.0),
	(8, 100002, 200003, '2021-06-11 11:24:37', 120.0),
	(9, 100003, 200003, '2021-06-05 11:24:44', 150.0);

/*为student_to_book字段创建数据*/
{
"id": 100001,
"creator": "陈老师",
"created": "2021-07-20 16:40:31",
"books": [
{
"id": 301089012318277,
"name": "凡人修仙传"
},
{
"id": 301089012318277,
"name": "魔天记"
}
],
"sharing": [
"张三",
"李四",
"王五"
],
"sharingId": [
100001,
100002,
100003
],
"status": 0
}

操作符

->

描述：获取JSON中某key的值。相当于 JSON_EXTRACT()。

案例：

/*获取字段中sharingId的值。*/
SELECT student_to_book-> "$.sharingId" sharingId FROM json_test
/*获取字段中sharingId的第二个值。*/
SELECT student_to_book-> "$.sharingId[1]" sharingId FROM json_test

函数

JSON_CONTAINS

描述：校验JSON中某key是否等于某值。

案例：

/*校验JSON某key是否等于值（特别注意字符串类型比较时要用'""'包裹。）。*/
SELECT JSON_CONTAINS(student_to_book, '100001', "$.id") isContain  FROM `json_test` ;
SELECT JSON_CONTAINS(student_to_book, '"陈老师"', "$.creator") isContain  FROM `json_test`;

JSON_CONTAINS_PATH

描述：校验JSON是否存在某些key。其中all表示所有key存在返回true，one表示任意key存在返回true。

案例：

/*校验该字段是否存在sharingId和sharing字段（所有字段存在返回1，否则返回0。）。*/
SELECT JSON_CONTAINS_PATH(student_to_book, 'all', "$.sharingId","$.sharing") isContain  FROM `json_test` 
/*校验该字段是否存在sharingId和idCard字段（任意一个字段存在返回1，否则返回0。）。*/
SELECT JSON_CONTAINS_PATH(student_to_book, 'one', "$.sharingId","$.idCard") isContain  FROM `json_test`

JSON_EXTRACT

描述：获取数组中的元素。

案例：

/*获取数组中第二个元素的值。*/
SELECT JSON_EXTRACT('[10, 20]', '$[1]') nodeValue;
SELECT JSON_EXTRACT('[[10, 20],[30, 40]]', '$[1]') array;
/*获取数组中第一、二个元素的值，返回的结构是数组。*/
SELECT JSON_EXTRACT('[10, 20]','$[0]', '$[1]') array;
SELECT JSON_EXTRACT('[[10, 20],[30, 40]]','$[0]', '$[1]') array;
/*获取二维数组中的第一组第二个元素。*/
SELECT JSON_EXTRACT('[[10, 20],[30, 40]]','$[0][1]') array;
/*获取数组中所有元素的值，返回的结构是数组。*/
SELECT JSON_EXTRACT('[10, 20,null,null]','$[*]') array;
/*获取字段中sharingId的值。*/
SELECT JSON_EXTRACT(student_to_book, "$.sharingId") sharingId FROM json_test
/*获取字段中sharingId的第二个值。*/
SELECT JSON_EXTRACT(student_to_book, "$.sharingId[1]") sharingId FROM json_test

JSON_KEYS

描述：获取JSON中的key，返回数组。

案例：

/*获取JSON的一级key。*/
SELECT JSON_KEYS(student_to_book ) FROM json_test 
/*获取JSON的二级key。*/
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') ;

JSON_OVERLAPS

描述：JSON和JSON匹配，或单个元素之间进行匹配。

注意：该函数在MySQL8.0.17中添加。

案例：

/*两个数组进行匹配（部分元素可以匹配得上则返回1）。*/
SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]");
SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");
SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');
/*两个元素进行比较。*/
SELECT JSON_OVERLAPS('5', '5');
SELECT JSON_OVERLAPS('"5"', '5');
SELECT JSON_OVERLAPS('"a"', '"a"');
/*JSON是否包含元素（注意数据类型）。*/
SELECT JSON_OVERLAPS('[4,5,6,7]', '6');
SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"');
SELECT JSON_OVERLAPS('["a","b"]', '"a"');

JSON_SEARCH

描述：查询JSON中值的位置，all返回所有可以匹配到值的位置，one只返回第一次匹配到值的位置。

案例：

/*获取JSON值的位置。*/
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'one', 'abc');
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc');
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10');
SELECT JSON_SEARCH('[1,2,3]', 'all', '4');
/*根据指定的value和位置条件模糊匹配数据，返回对应的位置。*/
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc', NULL, '$[*]');
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc', NULL, '$[2]');
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '%a%');

JSON_VALUE

描述：查询JSON中的值。

案例：

/*获取JSON的一级属性*/
SELECT JSON_VALUE('{"name": "张三", "age":12}', '$.age');
/*获取JSON的数组的值*/
SELECT JSON_VALUE('{"name": "张三", "age":12,"hobby":["篮球","小说"]}', '$.hobby');
SELECT JSON_VALUE('{"name": "张三", "age":12,"hobby":["篮球","小说"]}', '$.hobby[0]');
SELECT JSON_VALUE('["张三","李四","王五"]', '$[0]');
/*获取JSON的二级属性*/
SELECT JSON_VALUE(
'{"books":[{"id":301089012318277,"name":"凡人修仙传"},{"id":301089012318277,"name":"魔天记"}]}', 
'$.books[1].name'
);
/*获取JSON的属性并返回指定的数据类型*/
SELECT JSON_VALUE(
'{"item": "shoes", "price": "49.958"}', 
'$.price' RETURNING DECIMAL(4,2)
)  price;
/*
RETURNING只支持以下类型
FLOAT
DOUBLE
DECIMAL
SIGNED
UNSIGNED
DATE
TIME
DATETIME
YEAR （MySQL 8.0.22 及更高版本）不支持一位或两位数字的值。
CHAR
JSON
*/

MEMBER OF

描述：校验JSON是否包含某值。

案例：

SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');
SELECT "17" MEMBER OF('[23, "abc", 17, "ab", 10]');

JSON_ARRAY

描述：将各个元素转换成JSON数组。

案例：

SELECT JSON_ARRAY(4,5)

JSON_OBJECT

描述：将各个元素转换成JSON对象。

案例：

/*多条json数据*/
SELECT JSON_OBJECT( 
'courseId',courseId,'studentId',studentId,'create_time',create_time,'score',score) 
FROM `course_score`
/*一条json数据*/
SELECT CONCAT('[',GROUP_CONCAT(JSON_OBJECT( 
'courseId',courseId,'studentId',studentId,'create_time',create_time,'score',score)),']')
FROM `course_score`