Detailed explanation of JSON series operation functions in Mysql_MySQL

Foreword

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

The format of JSON is very simple: name/key value. In previous versions of MySQL, 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 practice it together.

Create table with JSON fields

For example, an 'article' table, the fields include

id, title title, tags

An article will 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;

Insert data

Insert a piece of data with JSON content and execute the insert statement:

INSERT INTO `article` (`title`, `tags`)

VALUES (

'体验 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

Use JSON functions to make two simple queries

1. Find all articles with the tag "Mysql"

SELECT * FROM `article`

WHERE JSON_CONTAINS(tags, '["Mysql"]');

2. Find articles starting with "Data" in the tag

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 find

2. Search range, there are two options, 'one' finds the first one that meets the conditions, 'all' finds all those that meet the conditions

3. Search conditions

JSON Path

JSON Path is used to locate the target field in the document, such as

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 represents the name field of the positioned document

JSON path starts with $, here are some more examples

{

"num": 123,

"arr": [1, 2],

"obj": {

"a": 3,

"b": 4

}

}

$.num //结果：123

$.arr //结果：[1, 2]

$.arr[1] //结果：1

$.obj.a //结果：3

$**.b //结果：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 the "Mysql" tag is already included, and there is no data for 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 another example, if you want to update the label "Mysql" to "Mysql 5.7.13", the update statement is as follows:

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

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

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

Insert data

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

Replace data

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

Append data to the end of the array

JSON_REMOVE(doc, path[, path]…)

Remove data from specified location

Through the initial operation experience, I feel that Mysql’s JSON operation is relatively smooth. It is indeed very convenient to use the document structure in Mysql in the future

The new version of Mysql also provides a Javascript console, which is similar to the MongoDB shell and is also very convenient, which will be summarized later. I hope this article will be helpful to everyone when learning mysql. Thank you for your support.