Detailed explanation of examples of JSON function operations in Mysql5.7

This article shares with you how to use json functions in mysql5.7 and related examples. It is very practical. Friends in need can refer to it

Preface

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 do it in practice.

Create a table with JSON fields

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

id, title title, tags

an article There will be multiple tags, 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 For data with JSON content, 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, you can see the newly inserted data

Query

Use JSON function to make two simple queries

1. Find the tags All articles of "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. The document to be found

2. The search range, there are two options, 'one' finds the first one that meets the conditions, ' all'Find all matching 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 the JSON extraction function, $.name is a JSON path, indicating the name field of the positioned document

JSON path It 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 examples 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

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' ) ;<br>

I have experienced JSON_MERGE and JSON_SET above, and there are many 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]…)<br>

Remove data from the specified location

Through the initial operation experience, I feel that Mysql’s JSON operation is relatively smooth. In the future, it can be used in mysql It is really convenient to use the document structure

The above is the detailed content of Detailed explanation of examples of JSON function operations in Mysql5.7. For more information, please follow other related articles on the PHP Chinese website!