MySQL对JSON的正确操作方法

╰+攻爆jí腚メ 2022-09-05 04:20 186阅读 0赞

**目录**

版本

目标

创建数据

操作符

\->

函数

JSON\_CONTAINS

JSON\_CONTAINS\_PATH

JSON\_EXTRACT

JSON\_KEYS

JSON\_OVERLAPS

JSON\_SEARCH

JSON\_VALUE

MEMBER OF

JSON\_ARRAY

JSON\_OBJECT

--------------------

# 版本 #

***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`