Description of JSON operation functions in Mysql5.7, mysql5.7json

Description of JSON operation functions in Mysql5.7, mysql5.7json

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"]');

Here we insert a JOSN array ["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. $. name is a JSON path, which indicates locating the name field of the document.

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

Query example using JSON path

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


The above experiences JSON_MERGE and JSON_SET. There 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.