How to use the new JSON type in MySQL5.7

To create a table json_test:

CREATE TABLE json_test (ID INT (one) auto_increment PRIMARY key,person_desc json) ENGINE INNODB;

Insert a record:

Insert into json_test (PERSON_DESC)  VALUES  (' {       ') 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"       }]   } ');

See what key is inserted in this line of 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)

You can see that there are three keys in it, authors,musicians,programmers for each one. Now find a key to take the corresponding value out:

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)

List Detailed values:

Mysql> Select, Json_extract (AUTHORS, ' $.firstname[0] ') as "FirstName", Json_extract (AUTHORS, ' $.last Name[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

Delete all objects that correspond 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, the discovery 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)

How to use the new JSON type in MySQL5.7