MySQL Json type

1 Introduction to Json type

After MySQL 5.7, the Json type is provided, which is a type designed by MySQL combining structured storage and unstructured storage.

In some scenarios, the Json type is simply gospel.

Scenario 1: User portrait, label describing the user and other similar scenarios, such as the patient health file of the Internet hospital system. There are many information that are not required, such as: height, weight, measurements, etc., can be stored in Json.

Scene 2: Game scene;

Scene 3: Store ancillary information such as pictures, such as picture resolution, picture title, etc.

2 Let's see how Json is used

Create a table and insert data

CREATE TABLE UserLogin (
    userId BIGINT NOT NULL,
    loginInfo JSON,
    PRIMARY KEY(userId)
);
INSERT INTO `UserLogin`(`userId`, `loginInfo`) VALUES (1, '{\"QQ\": \"82946772\", \"wxchat\": \"破产码农\", \"cellphone\": \"13918888888\"}');
INSERT INTO `UserLogin`(`userId`, `loginInfo`) VALUES (2, '{\"cellphone\": \"15026888888\"}');
INSERT INTO `UserLogin`(`userId`, `loginInfo`) VALUES (3, '{\"QQ\": \"82946772\", \"wxchat\": \"破产码农\", \"cellphone\": \"13918888889\"}');

2.1 JSON_EXTRACT function to get the value of a specific attribute in the Json field

SELECT JSON_UNQUOTE(JSON_EXTRACT(loginInfo, "$.cellphone")) from UserLogin;

Get the value of the cellphone attribute. You can use -> wording or ->> wording.

-- 带引号
SELECT loginInfo->"$.cellphone" from UserLogin;
-- 不带引号
SELECT loginInfo->>"$.cellphone" from UserLogin;

Function description:

JSON_EXTRACT (or ->) returns data from a JSON document.

JSON_UNQUOTE dereferences the JSON value and returns the result as a utf8mb4 string.

2.2 JSON_CONTAINS Query records in Json that satisfy cellphone equal to 13918888888

SELECT * from UserLogin where JSON_CONTAINS(loginInfo, '"13918888888"', '$.cellphone')

Description: Use JSON_CONTAINS search for whether the value of the specified key matches the specified value.

2.3 Add an index to the field in Json

-- 增加虚拟列-cellphone，值通过loginInfo计算而来
alter table UserLogin add COLUMN cellphone varchar(50) as (loginInfo->>"$.cellphone");
-- 给cellphone 这一列增加唯一索引
alter table UserLogin add unique index idex_cellphone(cellphone);

You can see that the index is indeed used for query

2.4 JSON_CONTAINS_PATH determines whether there is a corresponding field in Json

How many records in all records contain the wxchat field

SELECT count(*), JSON_CONTAINS_PATH(loginInfo, 'one', '$.wxchat') cp FROM UserLogin GROUP BY cp

return

Note that there are two records that contain the wxchat field, and one record that does not contain the wxchat field.

2.5 JSON_PRETTY makes Json look better

SELECT JSON_PRETTY(loginInfo) from UserLogin 

Can return formatted json data

{
  "QQ": "82946772",
  "wxchat": "破产码农",
  "cellphone": "13918888888"
}

2.6 JSON_STORAGE_SIZE returns the number of bytes in binary representation

Returns the number of bytes in the binary representation stored in the loginInfo field.

SELECT max(JSON_STORAGE_SIZE(loginInfo)) FROM UserLogin;
SELECT avg(JSON_STORAGE_SIZE(loginInfo)) FROM UserLogin;
SELECT min(JSON_STORAGE_SIZE(loginInfo)) FROM UserLogin;

2.7 Other functions

• JSON_OBJECT calculates a list of key-value pairs and returns a JSON object containing these key-value pairs, using JSON_OBJECT.
• JSON_OBJECTAGG accepts two column names or expressions, and returns a JSON object containing JSON_OBJECTAGG key-value pairs.
• JSON_ARRAY calculates a list of values and uses JSON_ARRAY to return a JSON array containing these values.
• JSON_ARRAYAGG aggregates the result set into a single JSON array whose elements consist of rows with JSON_ARRAYAGG.
• JSON_TABLE extracts data from a JSON document and returns it as a relational table with columns specified by JSON_TABLE.

to sum up

The JSON type is a new data type added to MySQL 5.7. Good use of the JSON data type can effectively solve many practical problems in the business. Finally, let me summarize today’s key content: Use JSON data type, MySQL version 8.0.17 or above is recommended, which has better performance and also supports Multi-Valued Indexes;

• The advantage of the JSON data type is that there is no need to define columns in advance, and the data itself is very descriptive;
• Do not use JSON to store data that has obvious relationships, such as user balance, user name, user ID, etc. These are all data that each user must include;
• The JSON data type is recommended for static data storage that is not frequently updated.

reference:

1、30 mins with MySQL JSON functions：https://dasini.net/blog/2018/07/23/30-mins-with-mysql-json-functions/

2. Lagou Education "Jiang Chengyao's MySQL Practical Collection"-04 | Non-structural storage: Use JSON well This card: https://kaiwu.lagou.com/course/courseInfo.htm?courseId=869#/detail/pc ?id=7320