Shulou(Shulou.com)06/01 Report--

This article gives an example of the usage of the json field type added by mysql5.7. Share with you for your reference, the details are as follows:

First, 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

Retrieve the fields of the json column

Retrieve the fields of the json column by using the-> or-> > operators

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 quotation marks.

Third, deal with some functions of json

JSON_PRETTY (json_val) displays json values in an elegant format

Select id, JSON_PRETTY (json) from json_test\ G

JSON_CONTAINS (target, candidate [, path]) determines whether a given candidate is included in the target, and if path is specified, it looks in the specified path.

Note that if the candidate here is a number, it needs to be wrapped in single quotation marks, and if it is a string, enclose it in double quotation marks.

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

JSON_CONTAINS_PATH (json_doc, one_or_all, path [, path]...) To judge whether the path in json_doc exists, the popular point is whether the key in json exists.

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

The second parameter, 'one', indicates that 1 is returned as long as a key exists, otherwise 0

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

The second parameter, 'all', indicates that all key exists before returning 1, otherwise 0

JSON_SET (json_doc, path, val [, path, val]...) Insert or update data and return results

Select JSON_SET (json,'$[0] .name', 'basket 2','$[0] .test', 'test') from json_test

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

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

Select JSON_INSERT (json,'$[0] .name', 'basket 2','$[0] .exts', 'extension') from json_test

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

JSON_REPLACE (json_doc, path, val [, path, val]...) Replace the existing value and return the result

Select JSON_REPLACE (json,'$[0] .name', 'replace') from json_test

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

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

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

Delete the data $[0] .name

JSON_KEYS (json_doc [, path]) gets 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]) gets 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 under the specified path of the json document

JSON_ARRAY ([val [, val]...]) Create an json array

Select JSON_ARRAY (1,'2, true, 5. 6, null, now ())

JSON_OBJECT ([key, val [, key, val]...]) Create a json object through key-value pairs

Select JSON_OBJECT ('name',' xiaoxu', 'age', 28,' height', 1.72)

Note that keys and values appear in pairs here

JSON_MERGE_PATCH (json_doc, json_doc [, json_doc]...) Merge json documents, if there is a duplicate key, the following data overrides the previous

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

JSON_MERGE_PRESERVE (json_doc, json_doc [, json_doc]...) Merge json documents. If there is a duplicate key, the values are saved through the array.

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

JSON_QUOTE (string) by wrapping and escaping internal quotation marks and other characters in double quotation mark characters

Select JSON_QUOTE ('Hello World')

JSON_UNQUOTE (json_val) converts escaped characters back to normal characters

Select JSON_UNQUOTE ('Hello\\ t\ "World\')

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.