MySQL 5.7 JSON type usage details

MySQL 5.7 JSON type usage details

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

The format of JSON is very simple: name/key-value. In previous MySQL versions, to implement such storage, either VARCHAR or TEXT large text was used. After the release of MySQL 5.7, the JSON data type and the retrieval and other function parsing of this type were specially designed. Let's first look at JSON access in older versions of MySQL.

Example table structure:

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

Let's insert a record:

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" 
  }] 
}');

Generally speaking, if we encounter such storage in JSON format, we can only take out this record and hand it over to an application, which will parse it.

Now in MySQL 5.7, we modify the following 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"] 
 row in set (0.00 sec)

We can see that there are three keys, namely authors, musicians, and programmers. Now find a KEY and get 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" 
 row in set (0.00 sec)

Let's further demonstrate how to delete all objects corresponding to the KEY authors.

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

I searched for the corresponding KEY and found that it had been deleted.

mysql> SELECT json_contains_path(person_desc,'all','$.authors') as authors_exist 
s FROM json_test_G 
*************************** 1. row *************************** 
authors_exists: 0 
 row in set (0.00 sec)

In summary, although MySQL 5.7 began to support the JSON data type, I suggest that if you want to use it, it is best to take out such values ​​and then calculate them in the application section. After all, the database is used to process simple data.

Summarize

The above is a detailed explanation of the use of MySQL 5.7 JSON type introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!

You may also be interested in:
  • Basic JSON Operation Guide in MySQL 5.7
  • Instructions for using JSON operation functions in Mysql5.7
  • A brief discussion on MySQL 5.7 JSON format retrieval
  • How to query json in the database in mysql5.6 and below
  • Detailed explanation of JSON series operation functions in Mysql
  • MySQL operations: JSON data type operations
  • MySQL insert json problem
  • Detailed example of MySQL (5.6 and below) parsing JSON
  • Example analysis of the usage of the new json field type in mysql5.7

<<:  Steps for Docker to build its own local image repository

>>:  Detailed installation tutorial of Docker under CentOS

Recommend

How to use async and await in JS

Table of contents 1. async 2. await: 3. Comprehen...

Using zabbix to monitor the ogg process (Windows platform)

This article introduces how to monitor the ogg pr...

React Hooks Detailed Explanation

Table of contents What are hooks? Class Component...

mysql 5.7.11 winx64.zip installation and configuration method graphic tutorial

Install and configure the MySql database system. ...

Tutorial on installing MYSQL8.0 on Alibaba Cloud ESC

Open the connection tool. I use MobaXterm_Persona...

How to modify the scroll bar style in Vue

Table of contents First of all, you need to know ...

A super detailed Vue-Router step-by-step tutorial

Table of contents 1. router-view 2. router-link 3...

MySQL transaction autocommit automatic commit operation

The default operating mode of MySQL is autocommit...

Four modes of Oracle opening and closing

>1 Start the database In the cmd command windo...

HTML table tag tutorial (12): border style attribute FRAME

Use the FRAME property to control the style type ...

Detailed analysis of MySQL master-slave delay phenomenon and principle

1. Phenomenon In the early morning, an index was ...

Detailed explanation of Vue form binding and components

Table of contents 1. What is two-way data binding...

Solution to 404 error when downloading apk file from IIS server

Recently, when using IIS as a server, the apk fil...

How to configure /var/log/messages in Ubuntu system log

1. Problem Description Today I need to check the ...