The JSON series operation functions in Mysql _mysql

Objective

JSON is a lightweight data interchange format that uses a language-independent text format, like XML, but is simpler, easier to read, and easier to write than XML. Easy to parse and generate for machines, and reduce network bandwidth transmission.

The format of JSON is very simple: name/Key value . In the MySQL version before the implementation of such storage, either with varchar or text. After the release of MySQL5.7, the JSON data types are specifically designed, as well as for this type of retrieval and other function parsing.

Here is a practical operation.

To create a table with a JSON field

such as an ' article ' Table with fields including

IDs, titles title, tags tags

An article will have multiple tags, tags can be set to the JSON type

The table statement is as follows:

CREATE TABLE ' article ' (

 ' id ' mediumint (8) unsigned not NULL auto_increment,

' title ' varchar ' is not NULL,

 ' Tags ' json DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb;

Inserting data

Inserts a data with JSON content and executes the INSERT statement:

INSERT into ' article ' (' title ', ' tags ')

VALUES (

' experience MySQL JSON ',

' [' MySQL ', ' Database '] '

);

This inserts a JOSN array. [“Mysql”, “Database”]

Querying all the contents of the article table, you can see the newly inserted data

Inquire

Use the JSON function to do two simple queries

1. Find all articles with tagged "Mysql"

SELECT * from ' article '

WHERE json_contains (tags, ' [Mysql] ');

2. Find an article in the label that starts with "Data"

SELECT * from ' article '

WHERE json_search (tags, ' one ', ' data% ') are not NULL;

The meaning of 3 parameters in the Json_search function:

1. Documents to look for

2. Find the range, there are two options, ' one ' to find the first qualified, ' all ' to find all eligible

3. Conditions of the search

JSON Path

JSON path is used to locate a target field in a document, such as

SELECT json_extract (

' {' id ': 1, ' name ': ' MySQL '} ',

' $.name '

);

The result is:MySQL

JSON_EXTRACT()is the JSON extraction function, which $.name is the JSON path name field that represents the location document

JSON pathStart with $ and see a few more examples below

{

"num": 123,

"arr": [1, 2],

"obj": {

"a": 3,

"B": 4

}

}

$.num//Result: 123

$.arr//results: [1, 2]

$.arr[1]//Result: 1

$.obj.a//Result: 3

$**.b//Result: 4

JSON pathexamples of queries used

Select

tags-> "$[0]" as ' tag ' from

' article ';

Update data

For example, to add a "dev" tag to the article, the update condition is that the "Mysql" tag is already included and there is no "dev" tag data

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, to update the "MySQL" tab to "MySQL 5.7.13″," Update the statement as follows:

UPDATE ' article ' Set tags = json_set (tags, ' $[0] ', ' Mysql 5.7.13 ');

There are a JSON_MERGE JSON_SET number of functions that have been experienced and that are used to modify 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 tail of an array

Json_remove (Doc, path[, path] ...)

Remove data from a specified location

Through the initial operation experience, feel the MySQL operation of the JSON is relatively smooth, the future can be used in MySQL document structure is really very convenient

The new version of Mysql also provides a Javascript console, similar to the MongoDB shell, is also very convenient, will be summed up later. I hope this article will help you in learning MySQL, thank you for your support to the cloud-dwelling community.