Detailed description of the JSON series operation functions in Mysql, detailed description of the mysqljson Functions

Detailed description of the JSON series operation functions in Mysql, detailed description of the mysqljson Functions

Preface

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

JSON format is very simple:Name/key value. In MySQL earlier versions, such storage must be implemented by using either VARCHAR or TEXT. After MySQL5.7 was released, it specifically designed the JSON data type, retrieval of this type, and other function parsing.

Next we will perform the following operations.

Create a table with JSON Fields

For example, in a 'Article 'table, the fields include

Id, title, label tags

An article has multiple tags, and the tags can be set to the 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 ('experience Mysql json', '["Mysql", "Database"]');

A josn array is inserted here. [“Mysql”, “Database”]

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

Query

Use JSON functions for two simple queries

1. Search for all articles with the tag "Mysql"

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

2. Search for Articles Whose tags start with "Data"

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

Meanings of the three parameters in the JSON_SEARCH function:

1. Document to be searched

2. The search range has two options: 'one' to find the first qualified, 'all' to find all qualified

3. SEARCH CRITERIA

JSON Path

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

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

Result:Mysql

JSON_EXTRACT()Is a JSON extraction function,$.nameIsJSON pathTo locate the name field of the document.

JSON pathIt starts with $. Let's take a few more examples.

{& Quot; num & quot;: 123, & quot; arr & quot;: [1, 2], & quot; obj & quot;: {& quot; a & quot;: 3, & quot; B & quot;: 4 }}$. num // result: 123 $. arr // result: [1, 2] $. arr [1] // result: 1 $. obj. a // result: 3 $ **. B // result: 4

UseJSON pathQuery example

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

Update Data

For example, if you want to add a "dev" tag to an article, update the data that includes the "Mysql" tag and does not have the "dev" tag.

The update statement is as follows:

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

The "dev" tag is successfully added.

For example, to update the "Mysql" label to "Mysql 5.7.13", the update statement is as follows:

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

Experience aboveJSON_MERGE AndJSON_SETThere are many other functions used to modify JSON, such:

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 a specified location

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

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