JSON operations in MySQL

- introduction
- 1. Create tables and add data
- 2.Basic query
- - 2.1 General json query
  - 2.2 Multiple condition query
  - 2.4 Related table query
- 3.JSON function operation
- - 3.1 ->, ->>Differences
  - 3.2 JSON_EXTRACT function: return the desired fields from json
  - 3.3 JSON_CONTAINS(): Whether JSON format data contains specific objects in fields
  - 3.4 JSON_OBJECT(): Convert a list of key-value pairs into a json object
  - 3.5 JSON_ARRAY(): Create JSON array
  - 3.6 JSON_TYPE(): Query the attribute type of a certain json field
  - 3.7 JSON_KEYS(): array of keys in JSON document
  - 3.8 JSON_SET(): Insert data into JSON format. If there is a key, replace it. If there is no key, add it.
  - 3.9 JSON_INSERT(): Insert values (insert new values into json, but do not replace existing old values)
  - 3.10 JSON_REPLACE()
  - 3.11 JSON_REMOVE(): Remove data from JSON document

Introduction

Mysql 5.7 and later provides a native Json type. Json values will no longer be stored in the form of strings, but in an internal binary format that allows fast reading of text elements (document elements). Json text will be automatically verified when inserting or updating a Json column. Text that fails verification will generate an error message. Json text is created in a standard way, and most comparison operators can be used for comparison operations, such as: =, <, <=, >, >=, <>, != and <=>.
Operating json in mybatis

1. Create tables and add data

--First create a simple database table in json format, in which json_value is a field in json format.
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;
--adding data
insert into dept VALUES(1,'Department 1','{"deptName": "Department 1", "deptId": "1", "deptLeaderId": " 3"}');
insert into dept VALUES(2,'Department 2','{"deptName": "Department 2", "deptId": "2", "deptLeaderId": " 4"}');
insert into dept VALUES(3,'Department 3','{"deptName": "Department 3", "deptId": "3", "deptLeaderId": " 5"}');
insert into dept VALUES(4,'Department 4','{"deptName": "Department 4", "deptId": "4", "deptLeaderId": " 5"}');
insert into dept VALUES(5,'Department 5','{"deptName": "Department 5", "deptId": "5", "deptLeaderId": " 5"}');

2.Basic query

Usage tips:
If the json string is not an array, use $.field name directly.
If the json string is an array [Array], use $[index id of the corresponding element] directly.

2.1 General json query

Use json field name->'$.json attribute' to perform query conditions

--Query the data of deptLeaderId=5
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';

2.2 Multiple condition query

--Check the data whose dept is "Department 3" and deptLeaderId=5, the sql is as follows:
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and dept='Department 3';

2.4 Related table query

--Create another table containing json format
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 some test data
insert into dept_leader VALUES(1,'leader1','{"name": "Wang Yi", "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 "}');
--Join the table to query the details of the department leader in dept_leader in the dept table
SELECT * from dept,dept_leader
WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;

3.JSON function operation

The json in the above query is the entire json data, which does not seem very convenient. So what if we only want to see a certain field in the json?

3.1 ->, ->>Difference

-> will maintain the original format in the json document format, but ->> will remove all quotation marks.
Special note: When using -> as a where query, you need to pay attention to the type. ->> does not need to pay attention to the type.

select * from dept where json_value->'$.deptId'=1

select * from dept where json_value->'$.deptId'='1'

3.2 JSON_EXTRACT function: return the desired fields from json

1. You can query the value through key (if it is a json array type, you can get the value at the corresponding position through the subscript), which is very convenient.

2. Usage scenario: JSON_EXTRACT performance verification. By viewing the execution plan, verify that all are full table scans. If the amount of data is small and the json string is large, it can be used. If the amount of data is large, it is not recommended.

3. Syntax: JSON_EXTRACT(json_doc, path[, path] …)

4. Chestnut

select
json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run\ ","sing"]}',"$.name") as name,
json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run\ ","sing"]}',"$.tel_no") as tel_no,
json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run\ ","sing"]}',"$.hobbies[0]") as hobby_1,
json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run\ ","sing"]}',"$.hobbies[1]") as hobby_2,
json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run\ ","sing"]}',"$.hobbies[2]") as hobby_3,
json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run\ ","sing"]}',"$.hobbies[3]") as hobby_4;

result:

Example query:

-- Create test table
CREATE TABLE `tab_json` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key id',
  `data` json DEFAULT NULL COMMENT 'json string',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Add new data
-- {"Tel": "132223232444", "name": "david", "address": "Beijing"}
-- {"Tel": "13390989765", "name": "Mike", "address": "Guangzhou"}
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (1, '{"Tel": "132223232444", "name ": "david", "address": "Beijing"}');
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (2, '{"Tel": "13390989765", "name ": "Mike", "address": "Guangzhou"}');
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (3, '{"success": true,"code": "0","message" : "","data": {"name": "jerry","age": "18","sex": "male"}} ');
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (4, '{"success": true,"code": "1","message" : "","data": {"name": "tome","age": "30","sex": "female"}} ');

-- Inquire
select * from tab_json;

-- json_extract
select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.tel");
select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.name");

--Use the json_extract function on the tab_json table
select json_extract(data,'$.name') from tab_json;

#If you query for a key that does not exist, you can query it, but NULL will be returned.
select json_extract(data,'$.name'),json_extract(data,'$.Tel') from tab_json;
select json_extract(data,'$.name'),json_extract(data,'$.tel') from tab_json;
select json_extract(data,'$.name'),json_extract(data,'$.address') from tab_json;

--Conditional query
select json_extract(data,'$.name'),json_extract(data,'$.Tel') from tab_json where json_extract(data,'$.name') = 'Mike';

-- Nested json query
select * from tab_json where json_extract(data,'$.success') = true;
select json_extract(data,'$.data') from tab_json where json_extract(data,'$.success') = true;
-- Query data corresponding to the value whose key is name in json
select json_extract( json_extract(data,'$.data'),'$.name') from tab_json where json_extract(data,'$.code') = "1";
select json_extract( json_extract(data,'$.data'),'$.name'),json_extract( json_extract(data,'$.data'),'$.age') from tab_json where json_extract(data,'$.code') = "0";

-- Performance verification, after verification, all are full table scans. Usage scenarios: If the amount of data is small, it can be used if the json string is large, but if the amount of data is large, it is not recommended.
explain select * from tab_json where json_extract(data,'$.success') = true;
explain select json_extract(data,'$.data') from tab_json where json_extract(data,'$.success') = true;
-- Query data corresponding to the value whose key is name in json
explain select json_extract( json_extract(data,'$.data'),'$.name') from tab_json where json_extract(data,'$.code') = "1";
explain select json_extract( json_extract(data,'$.data'),'$.name'),json_extract( json_extract(data,'$.data'),'$.age') from tab_json where json_extract(data,'$.code') = "0";

3.3 JSON_CONTAINS(): Whether JSON format data contains specific objects in fields

Usage: JSON_CONTAINS(target, candidate[, path])

--Query objects containing deptName=department 5
select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","department 5"))

3.4 JSON_OBJECT(): Convert a list of key-value pairs into a json object

For example, we want to query the value in an object equal to
For example, we add this set of data to the dept table:

insert into dept VALUES(6,'Department 9','{"deptName": {"dept":"de","depp":"dd" }, "deptId": "5", "deptLeaderId": "5"}');

We can see that there is another object in deptName, which also has two attribute fields: dept and depp. So how should we query the employees with depp=dd.

Usage: JSON_OBJECT([key, val[, key, val] …])
case:

SELECT * from (
SELECT *,json_value->'$.deptName' as deptName FROM dept
) t WHERE JSON_CONTAINS(deptName,JSON_OBJECT("depp","dd"));

3.5 JSON_ARRAY(): Create JSON array

For example, we add this set of data to the dept table:

insert into dept VALUES(7,'Department 9','{"deptName": ["1","2","3"], "deptId\ ": "5", "deptLeaderId": "5"}');
insert into dept VALUES(7,'Department 9','{"deptName": ["5","6","7"], "deptId": \ "5", "deptLeaderId": "5"}');

Usage: JSON_ARRAY([val[, val] …])

Example: We want to query the data whose deptName contains 1

SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1"))

3.6 JSON_TYPE(): Query the attribute type of a certain json field

Usage: JSON_TYPE(json_val)
Example: For example, we want to query the field attributes of deptName.

SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept

3.7 JSON_KEYS(): Key array in JSON document

Usage: JSON_KEYS(json_value)
Example: For example, we want to query all keys in json format data

SELECT JSON_KEYS(json_value) FROM dept

The next three functions are all new data types:

JSON_SET(json_doc, path, val[, path, val] …)
JSON_INSERT(json_doc, path, val[, path, val] …)
JSON_REPLACE(json_doc, path, val[, path, val] …)

3.8 JSON_SET(): Insert data into JSON format, replace if there is a key, add new if there is no key

This is also a function that is often used in our development process.
Usage: JSON_SET(json_doc, path, val[, path, val] …)
Example: For example, we want to add a new group of data with id=2: newData: new data, modify deptName to the new department 1

update dept set json_value=JSON_SET('{"deptName": "Department 2", "deptId": "2", "deptLeaderId": "4"} ','$.deptName','New department 1','$.newData','New data') WHERE id=2;

Note: If json_doc does not contain the previous value of this cell, the previous value will be overwritten by the new value. For example, if we change the update statement to:

update dept set json_value=JSON_SET('{"a":"1","b":"2"}','$.deptName',' New department 1','$.newData','New data') WHERE id=2

We can see that the json_doc here is {“a”: “1”, “b”: “2”}. In this case, the previous cell value will be overwritten and then the cell field will be added/overwritten.

3.9 JSON_INSERT(): Insert values (insert new values into json, but do not replace existing old values)

Usage: JSON_INSERT(json_doc, path, val[, path, val] …)
case:

UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', ' New department 2','$.newData2','New data 2')
WHERE id=2

We can see that due to changes in json_doc, the previous values have been overwritten, and deptName and newData2 have been added.
If we execute the following SQL again and just change the value, we will see that the key value inside will not change.
Because this function is only responsible for inserting new values into json, but does not replace existing old values.

3.10 JSON_REPLACE()

Usage: JSON_REPLACE(json_doc, path, val[, path, val] …)
Example:
If we want to update the value of newData2 in the id=2 data: updated data 2

UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "New department 2 ", "newData2": "New data 2"}', '$.newData2', 'Updated data 2') WHERE id =2;

3.11 JSON_REMOVE(): Remove data from JSON document

Usage: JSON_REMOVE(json_doc, path[, path] …)
Example: Delete the field with key a.

UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "New department 2 ", "newData2": "Updated data 2"}','$.a') WHERE id =2;