Example analysis of the usage of the new json field type in mysql5.7

This article uses an example to illustrate the usage of the json field type added in MySQL 5.7. Share with you for your reference, the details are as follows:

1. Let's create a table and prepare some data

CREATE TABLE `json_test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `json` json DEFAULT NULL COMMENT 'json data',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. Retrieve fields of json column

Retrieve the fields of a json column by using the -> or ->> operator

select id, json->'$[0].items[0].name' from json_test;

select id, json->>'$[0].items[0].name' from json_test;

The difference between using -> and ->> is that the result is wrapped in quotes.

3. Some functions for processing json

JSON_PRETTY(json_val) displays the json value in an elegant format

select id, JSON_PRETTY(json) from json_test\G;

JSON_CONTAINS(target, candidate[, path]) Determines whether the given candidate is contained in the target. If path is specified, it searches in the specified path.

Note, note, note that if candidate is a number, it needs to be wrapped in single quotes. If it is a string, it needs to be wrapped in double quotes inside the single quotes.

select JSON_CONTAINS(json->'$[0].name', '"1号basket"') from json_test;
select JSON_CONTAINS(json, '"1号篮"', '$[0].name') from json_test;

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...) determines whether the path in json_doc exists, or in layman's terms, whether the key in json exists

select JSON_CONTAINS_PATH(json, 'one', '$[0].name', '$[0].test') from json_test;

The second parameter 'one' means that if there is a key, it returns 1, otherwise it returns 0

select JSON_CONTAINS_PATH(json, 'all', '$[0].name', '$[0].test') from json_test;

The second parameter 'all' means that 1 is returned only if all keys exist, otherwise 0

JSON_SET(json_doc, path, val[, path, val] ...) inserts or updates data and returns the result

select JSON_SET(json, '$[0].name', 'Basket No. 2', '$[0].test', 'test') from json_test;

We modify the value of $[0].name and add an item with the key test and the value test

JSON_INSERT(json_doc, path, val[, path, val] ...) Inserts data and returns the result, but does not replace existing values.

select JSON_INSERT(json, '$[0].name', 'Basket No. 2', '$[0].exts', 'Extension') from json_test;

At this time, $[0].name will not be updated, only a new field $[0].exts will be added

JSON_REPLACE(json_doc, path, val[, path, val] ...) Replaces an existing value and returns the result

select JSON_REPLACE(json, '$[0].name', 'Replace') from json_test;

Replace the value in $[0].name with the new value

JSON_REMOVE(json_doc, path[, path] ...) Delete data and return the result

select JSON_REMOVE(json, '$[0].name') from json_test;

Delete the data $[0].name

JSON_KEYS(json_doc[, path]) Get all the keys in the json document

select JSON_KEYS(json, '$[0]') from json_test;

Get all keys under the $[0] path

JSON_LENGTH(json_doc[, path]) Get the length of the json document

select JSON_LENGTH(json, '$[0]') from json_test;

Get the number of elements under $[0]

JSON_EXTRACT(json_doc, path[, path] ...) Returns the data in the json document

select JSON_EXTRACT(json, '$[0]') from json_test;
select JSON_EXTRACT(json, '$[0].name') from json_test;

Returns the data in the specified path of the json document

JSON_ARRAY([val[, val] ...]) creates a JSON array

select JSON_ARRAY(1, '2', true, 5.6, null, now());

JSON_OBJECT([key, val[, key, val] ...]) Create a JSON object from a key-value pair

select JSON_OBJECT('name', 'xiaoxu', 'age', 28, 'height', 1.72);

Note that the key and value must appear in pairs.

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...) merges json documents. If there are duplicate keys, the latter data will overwrite the former ones.

select JSON_MERGE_PATCH('{"name":"test1"}', '{"name":"test2"}');

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...) merges json documents. If there are duplicate keys, the values ​​will be saved in an array.

select JSON_MERGE_PRESERVE('{"name":"test1"}', '{"name":"test2"}');

JSON_QUOTE(string) escapes inner quotes and other characters by wrapping them in double quote characters

select JSON_QUOTE('Hello "World"');

JSON_UNQUOTE(json_val) Convert escape characters back to normal characters

select JSON_UNQUOTE('Hello\\t\"world\"');

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.