MySQL5.7 JSON Type Usage Introduction

JSON is a lightweight data interchange format that takes a language-independent text format, like XML, but is simpler, easier to read and easier to write than XML. It is easy for the machine to parse and generate, and will reduce the transmission of network bandwidth.
The format of JSON is very simple: name/key value. Before the MySQL version to implement such a storage, either with varchar or with text large text. After the release of MySQL5.7, the JSON data types were specifically designed, as well as about this type of retrieval and other function parsing. Let's look at the old MySQL version of JSON access first.


Sample Table structure:

CREATE TABLE json_test (id int,person_desc TEXT) ENGINE INNODB;

Let's insert a record:
I

Nsert 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 ":" Rachmaninoff ", "        instrument": "Piano"    }]} ');

That's what we're going to do. In order to store the JSON format, we can only take this record out to the application and have an application to parse it.




Now to the MySQL5.7, we re-modify the following table structure:

ALTER TABLE json_test MODIFY person_desc json;

Let's take a look at the key of the inserted JSON data:

Mysql> Select Id,json_keys (PERSON_DESC) as "Keys" from json_test\g*************************** 1. Row ***************************  Id:1keys: ["Authors", "musicians", "programmers"]1 row in Set (0.00 sec)

As we can see, there are three keys in it, each of which is authors,musicians,programmers. Now find a key to take the corresponding value out:

Mysql> Select Json_extract (AUTHORS, ' $.lastname[0] ') as ' name ', AUTHORS from    -and    SELECT Id,json_ Extract (Person_desc, ' $.authors[0][0] ') as "authors" from Json_test to UNION all,    Select Id,json_ Extract (Person_desc, ' $.authors[1][0] ') as "authors" from Json_test to 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 out 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 further demonstrate that all objects corresponding to the authors key are deleted.

Mysql> UPDATE json_test,    SET person_desc = Json_remove (Person_desc, ' $.authors ') \gquery OK, 1 row affected (0. SEC) Rows matched:1  changed:1  warnings:0

Find out the corresponding key, the discovery has been deleted.

Mysql> SELECT Json_contains_path (Person_desc, ' All ', ' $.authors ') as authors_exists from json_test\g*************** 1. Row ***************************authors_exists:01 row in Set (0.00 sec)

In summary, although MySQL5.7 begins to support JSON data types, I suggest that if you want to use it, it is best to take this value out and then calculate it in the application segment, after all, the database is used to process simple data.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL5.7 JSON Type Usage Introduction