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