Instructions for using JSON operation functions in Mysql5.7

Instructions for using JSON operation functions in Mysql5.7

Preface

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 put it into practice.

Create a table with a JSON field

For example, an 'article' table has fields including

id, title, tags

An article can have multiple tags, and tags can be set to JSON type

The table creation statement is as follows:

CREATE TABLE article` (
 id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
title` varchar (200) NOT NULL,
 tags` json DEFAULT NULL ,
PRIMARY KEY (`id`)
)ENGINE=InnoDB;

Inserting Data

To insert a data with JSON content, execute the insert statement:

INSERT INTO article` (`title`, `tags`)
VALUES (
'Experience Mysql JSON',
'["Mysql", "Database"]'
);

What is inserted here is a JOSN array ["Mysql", "Database"]

Query all the contents in the article table and you can see the newly inserted data.

Query

Using JSON functions to do two simple queries

1. Find all articles with the tag "MySQL"

SELECT * FROM article`
WHERE JSON_CONTAINS(tags, '["Mysql"]' );

2. Find articles with tags starting with "Data"

SELECT * FROM article`
WHERE JSON_SEARCH(tags, 'one' , 'Data%' ) IS NOT NULL ;

The meaning of the three parameters in the JSON_SEARCH function:

1. Documents to be found

2. There are two options for the search range: 'one' to find the first one that meets the conditions, and 'all' to find all that meet the conditions.

3. Search conditions

JSON Path

JSON Path is used to locate the target field in the document, for example

SELECT JSON_EXTRACT(
'{"id": 1, "name": "mysql"}' ,
'$.name'
);

The result is:mysql

JSON_EXTRACT() is a JSON extraction function. $.name is a JSON path, which means the name field of the located document.

JSON path starts with $. Let's look at some more examples.

{
"num" : 123,
"arr" : [1, 2],
"obj" : {
"a" : 3,
"b" : 4
}
}
$.num //Result: 123
$.arr //Result: [1, 2]
$.arr[1] //Result: 1
$.obj.a //Result: 3
$**.b //Result: 4

Query example using JSON path

SELECT
tags-> "$[0]" as 'tag'
FROM article`;

Update data

For example, if you want to add a "dev" tag to an article, the update condition is that it already contains the "MySQL" tag and there is no data with the "dev" tag yet.

The update statement is as follows:

UPDATE article`
SET tags = JSON_MERGE(tags, '["dev"]' )
WHERE
JSON_SEARCH(tags, 'one' , 'dev' ) IS NULL
AND
JSON_SEARCH(tags, 'one' , 'Mysql' ) IS NOT NULL ;

You can see that the "dev" tag was successfully added

For example, if you want to update the "Mysql" tag to "Mysql 5.7.13", the update statement is as follows:

UPDATE article` set tags = JSON_SET(tags, '$[0] ', 'Mysql 5.7.13' ) ;

We have experienced JSON_MERGE and JSON_SET above. There are many other functions for modifying JSON, such as:

JSON_INSERT(doc, path, val[, path, val]…)

Inserting Data

JSON_REPLACE(doc, path, val[, path, val]…)

Replace data

JSON_ARRAY_APPEND(doc, path, val[, path, val]…)

Append data to the end of an array

JSON_REMOVE(doc, path[, path]…)

Remove data from the specified location

Through the initial operation experience, I feel that the JSON operation of Mysql is relatively smooth. It is really convenient to use the document structure in MySQL in the future.

You may also be interested in:
  • Basic JSON Operation Guide in MySQL 5.7
  • MySQL 5.7 JSON type usage details
  • 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

<<:  Example of automatic import method of vue3.0 common components

>>:  Solution to the error when installing Docker on CentOS version

Recommend

MySQL 8.0.17 installation graphic tutorial

This article shares with you the MySQL 8.0.17 ins...

HTML elements (tags) and their usage

a : Indicates the starting or destination positio...

Example code for implementing the "plus sign" effect with CSS

To achieve the plus sign effect shown below: To a...

How to use selenium+testng to realize web automation in docker

Preface After a long time of reading various mate...

What are the attributes of the JSscript tag

What are the attributes of the JS script tag: cha...

Detailed tutorial on installing nacos in docker and configuring the database

Environment Preparation Docker environment MySQL ...

Example of writing mobile H5 to invoke APP (IOS, Android)

iOS 1. URL scheme This solution is basically for ...

How to use Vue-router routing

Table of contents 1. Description 2. Installation ...

Use viewport in meta tag to define screen css

<meta name="viewport" content="w...

Summary of Linux Logical Volume Management (LVM) usage

Managing disk space is an important daily task fo...

Implementation of mysql8.0.11 data directory migration

The default storage directory of mysql is /var/li...