1 Introduction to Json types

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

In some cases, the Json type is a blessing.

Scenario 1: User portrait, labels that describe users, and other similar scenarios, such as patient health records in Internet hospital systems, have many optional information, such as height, weight, and measurements, which can be stored using Json.

Scene 2: Game scene;

Scenario 3: Store images and other ancillary information, such as image resolution, image title, etc.

2 let’s see how Json works

Create the table and insert the data

CREATE TABLE UserLogin (
    userId BIGINT NOT NULL,
    loginInfo JSON,
    PRIMARY KEY(userId)
);
INSERT INTO `UserLogin`(`userId`, `loginInfo`) VALUES (1.'{\ \ "QQ" : \ "82946772 \" and \ "wxchat \" : \ "bankruptcy code farmers \", \ "cellphone \" : \ "13918888888 \"}');
INSERT INTO `UserLogin`(`userId`, `loginInfo`) VALUES (2.'{\"cellphone\": \"15026888888\"}');
INSERT INTO `UserLogin`(`userId`, `loginInfo`) VALUES (3.'{\ \ "QQ" : \ "82946772 \" and \ "wxchat \" : \ "bankruptcy code farmers \", \ "cellphone \" : \ "13918888889 \"}');
Copy the code

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

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

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

- quoted
SELECT loginInfo->"$.cellphone" from UserLogin;
-- Without quotes
SELECT loginInfo->>"$.cellphone" from UserLogin;
Copy the code

Function description:

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

JSON_UNQUOTE unquotes the JSON value and returns the result as a UTf8MB4 string.

2.2 JSON_CONTAINS Queries Json records that satisfy that cellphone is equal to 13918888888

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

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

2.3 Adding Indexes to A Field in the Json File

-- Add virtual column -cellphone, whose value is calculated by loginInfo
alter table UserLogin add COLUMN cellphone varchar(50) as (loginInfo->>"$.cellphone");
-- Add a unique index to the cellphone column
alter table UserLogin add unique index idex_cellphone(cellphone);
Copy the code

You can see that the index was indeed used for the query

2.4 JSON_CONTAINS_PATH Checks whether the CORRESPONDING field exists in the Json file

How many of all records contain the WxCHAT field

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

return

Note 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 long look better

SELECT JSON_PRETTY(loginInfo) from UserLogin 
Copy the code

You can return formatted JSON data

{
  "QQ": "82946772"."wxchat": "Bankrupt code farmer"."cellphone": "13918888888"
}
Copy the code

2.6 JSON_STORAGE_SIZE Returns the number of bytes in binary format

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;
Copy the code

2.7 Other Functions

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

conclusion

JSON is a new data type in MySQL 5.7. Using the JSON data type can effectively solve many practical problems in services. MySQL 8.0.17 or higher is recommended for performance and supports Multi-Valued Indexes.

  • The nice thing about JSON data types is that you don’t need to define columns up front, and the data itself is pretty descriptive;

  • Do not store data with JSON that has obvious relationships, such as user balance, user name, user ID card, etc., which are all data that must be included by each user.

  • The JSON data type is recommended for static data stores that do not update frequently.

Reference:

1, 30 mins with MySQL JSON functions: dasini.net/blog/2018/0…

2, retractor education “davidjiang MySQL real treasure dian” – 04 | unstructured storage: use JSON well this card: kaiwu.lagou.com/course/cour…