MySQL5.7 introduction to the JSON type

MySQL5.7 introduction to the JSON type

JSON is a lightweight data exchange format, which uses a language-independent text format, similar to XML, but easier to read and write than XML. It is easy for machines to parse and generate, and reduces network bandwidth transmission.

JSON format is very simple: Name/key value. In MySQL earlier versions, such storage must be implemented by using either VARCHAR or TEXT. After MySQL5.7 was released, it specifically designed the JSON data type, retrieval of this type, and other function parsing. Let's take a look at JSON access in earlier MySQL versions.

Example Table Structure:

Create table json_test (
Id INT,
Person_desc TEXT
) Engine innodb;

Insert a record:

Insert into json_test VALUES (1 ,'{
"Programmers ":[{
"FirstName": "Brett ",
"LastName": "McLaughlin ",
"Email": "aaaa"
},{
"FirstName": "Jason ",
"LastName": "Hunter ",
"Email": "bbbb"
},{
"FirstName": "Elliotte ",
"LastName": "Harold ",
"Email": "cccc"
}],
"Authors ":[{
"FirstName": "Isaac ",
"LastName": "Asimov ",
"Genre": "sciencefiction"
},{
"FirstName": "Tad ",
"LastName": "Williams ",
"Genre": "fantasy"
},{
"FirstName": "Frank ",
"LastName": "Peretti ",
"Genre": "christianfiction"
}],
"Musicians ":[{
"FirstName": "Eric ",
"LastName": "Clapton ",
"Instrument": "guitar"
},{
"FirstName": "Sergei ",
"LastName": "invalid maninoff ",
"Instrument": "piano"
}]
}');

In this case, we can only extract this record and submit it to an application for parsing.
 
Now we have reached MySQL5.7. Let's re-Modify the table structure:

Alter table json_test MODIFY person_desc json;

First, let's take a look at the keys of the inserted JSON data:

Mysql> SELECT id, json_keys (person_desc) as "keys" FROM json_test \ G
* *************************** 1. row ***************************
Id: 1
Keys: ["authors", "musicians", "programmers"]
1 row in set (0.00 sec)

We can see that there are three keys, namely authors, musicians, and programmers. Find a KEY and take out the corresponding value:

Mysql> SELECT json_extract (AUTHORS, '$. lastName [0]') AS 'name', AUTHORS FROM
-> (
-> SELECT id, json_extract (person_desc, '$. authors [0] [0]') AS "authors" FROM json_test
-> UNION ALL
-> SELECT id, json_extract (person_desc, '$. authors [1] [0]') AS "authors" FROM json_test
-> UNION ALL
-> SELECT id, json_extract (person_desc, '$. authors [2] [0]') AS "authors" FROM json_test
->) AS T1
-> Order by name desc \ G
* *************************** 1. row ***************************
Name: "Williams"
AUTHORS: {"genre": "fantasy", "lastName": "Williams", "firstName": "Tad "}
* *************************** 2. row ***************************
Name: "Peretti"
AUTHORS: {"genre": "christianfiction", "lastName": "Peretti", "firstName": "Frank "}
* *************************** 3. row ***************************
Name: "Asimov"
AUTHORS: {"genre": "sciencefiction", "lastName": "Asimov", "firstName": "Isaac "}
3 rows in set (0.00 sec)

Now let's list the detailed values:

Mysql> SELECT
-> Json_extract (AUTHORS, '$. firstName [0]') AS "firstname ",
-> Json_extract (AUTHORS, '$. lastName [0]') AS "lastname ",
-> Json_extract (AUTHORS, '$. genre [0]') AS "genre"
-> FROM
-> (
-> SELECT id, json_extract (person_desc, '$. authors [0]') AS "authors" FROM json
_ Test
->) As t \ G
* *************************** 1. row ***************************
Firstname: "Isaac"
Lastname: "Asimov"
Genre: "sciencefiction"
1 row in set (0.00 sec)

We will further demonstrate removing all objects corresponding to the authors KEY.

Mysql> UPDATE json_test
-> SET person_desc = json_remove (person_desc, '$. authors') \ G
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Find the corresponding KEY and find it has been deleted.

Mysql> SELECT json_contains_path (person_desc, 'all', '$. authors') as authors_exist
S FROM json_test \ G
* *************************** 1. row ***************************
Authors_exists: 0
1 row in set (0.00 sec)

To sum up, although MySQL5.7 supports the JSON data type, we recommend that you extract the value and calculate it in the Application Section, after all, databases are used to process simple data.

This article permanently updates the link address: