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 path
Start 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 path
examples 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.