How to use the new JSON type in MySQL5.7

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.