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.