1. Introduction to Jason type

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

In some situations, the Jason type is a blessing.

Scenario 1: user portraits, labels describing users and other similar scenarios, such as patient health files in Internet hospital systems, many information is not required, such as height, weight, circumference and so on, which can be stored by Json.

Scenario 2: game scenario;

Scenario 3: store pictures and other ancillary information, such as picture resolution, picture title, etc.

Let's see how Jason uses it

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\": \"Bankruptcy code farmer\", \"cellphone\": \"13918888888\"}');
INSERT INTO `UserLogin`(`userId`, `loginInfo`) VALUES (2, '{\"cellphone\": \"15026888888\"}');
INSERT INTO `UserLogin`(`userId`, `loginInfo`) VALUES (3, '{\"QQ\": \"82946772\", \"wxchat\": \"Bankruptcy code farmer\", \"cellphone\": \"13918888889\"}');

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

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

Gets the value of the cellphone property. You can use - > or - > >.

-- Quoted
SELECT loginInfo->"$.cellphone" from UserLogin;
-- Without quotation marks
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 queries the records in Json that satisfy that the cell phone is equal to 13918888888

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

Description: use JSON_CONTAINS searches whether the value of the specified key matches the specified value.

2.3 adding indexes to fields in Json

-- Add virtual column-cellphone，Value pass loginInfo Calculated from
alter table UserLogin add COLUMN cellphone varchar(50) as (loginInfo->>"$.cellphone");
-- to cellphone Add a unique index to this column
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 wxchat fields

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

return

Note: there are two records including wxchat field and one record not included.

2.5 JSON_PRETTY makes Jason look better

SELECT JSON_PRETTY(loginInfo) from UserLogin 

You can return formatted json data

{
  "QQ": "82946772",
  "wxchat": "Bankruptcy code farmer",
  "cellphone": "13918888888"
}

2.6 JSON_STORAGE_SIZE returns the number of bytes in binary representation

Returns the number of bytes of 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 the list of key value pairs and returns the JSON object containing these key value pairs, using JSON_OBJECT.
• JSON_OBJECTAGG accepts two column names or expressions and returns one containing JSON_ JSON object for objectagg key value pair.
• JSON_ARRAY calculates a list of values and uses JSON_ARRAY returns a JSON array containing these values.
• JSON_ARRAYAGG aggregates the result set into a single JSON array whose elements are composed of JSON_ARRAYAGG consists of rows.
• JSON_TABLE extracts data from a JSON document and treats it as having JSON_TABLE specifies the relational table return for the column.

summary

JSON type is a new data type in MySQL version 5.7. Making good use of JSON data type can effectively solve many practical problems in business. Finally, I would like to summarize today's key contents: using JSON data types, MySQL version above 8.0.17 is recommended, which has better performance and supports multi valued indexes;

• The advantage of 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 with obvious relationship, such as user balance, user name, user ID card, etc., which must be included by each user;

• JSON data types are recommended for static data stores that are not updated frequently.

reference resources:

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

2. Hook Education: Jiang Chengyao's MySQL actual combat classic-04 | unstructured storage: make good use of JSON: https://kaiwu.lagou.com/course/courseInfo.htm?courseId=869#/detail/pc?id=7320