MySQL 5.7 provides a new field format json, which is probably because MySQL wants to eat all non relational and relational databases, so it has introduced this very easy-to-use format. In this way, many of our businesses based on mongoDb or clickHouse can be implemented with MySQL. Of course, version 5.7 is only the most basic version, and the efficiency of massive data is far from enough, but these are solved in mysql8.0. Today, we will briefly introduce the json data format operation of MySQL

JSON The array is separated by commas and contained in[and] List of values in characters:
["abc", 10, null, true, false]
One JSON Object contains a set of comma separated and contained in{and }Key value pairs in characters:
{"k1": "value", "k2": 10}

1, Function to create JSON value
JSON_ARRAY([val[, val] ...])
Evaluate the (possibly empty) list of values and return a JSON array containing these values.

JSON_OBJECT([key, val[, key, val] ...])
Evaluate the list of key value pairs (which may be empty) and return the JSON object containing these pairs. If any key name is NULL or the number of parameters is odd, an error occurs.

JSON_QUOTE(string)
Reference the utf8mb4 string as a JSON value by wrapping the string with double quote characters and escaping internal quotes and other characters, and then return the result as a string. Null returns NULL if the parameter is.

2, Function to search for JSON values
JSON_CONTAINS(target, candidate[, path])
Determine whether a json value is included

SELECT * FROM json where JSON_CONTAINS(jsonData,'{"user_name":"tom"}');

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
Determine whether to package json value under a path

MySQL Inside json Divided into json array and json object.  $Represents the whole json Object that uses subscripts when indexing data(about json array，Start from 0)Or key value(about json object，With special characters key To use"Enclose, for example $."my name"). 
For example:[3, {"a": [5, 6], "b": 10}, [99, 100]]，Then:
$[0]: 3
$[1]:  {"a": [5, 6], "b": 10}
$[2] : [99, 100]
$[3] :  NULL

SELECT id,jsonData,JSON_CONTAINS_PATH(jsonData, 'one', '$.user_name') as json FROM json;

SELECT JSON_CONTAINS_PATH(jsonData, 'one', '$.bbs') as json FROM json;

json_extract (extract JSON value) is a concise way to write
column->path

Id        jsonData
1	   [{"a": [5, 6], "b": 10}]
2	   {"age": "23", "sex": "1", "user_name": "tom"}
3	   [{"user_name": "bob"}, {"user_name": "jack"}]

SELECT JSON_EXTRACT(jsonData, '$.user_name') json_extract FROM json where id = 2;

In MySQL 5.7.9 and later, when used with two parameters, - > operator is used as JSON_ The alias of the extract() function, the column identifier on the left and the JSON path (string text) on the right are based on the JSON document (column value).

SELECT jsonData -> '$.user_name' json_extract FROM json where id = 2;

SELECT jsonData -> '$[0].user_name' json_extract FROM json where id = 3;

json_ The concise way of unquote:
column->>path

Remove the quotation marks from the json string and convert the value to string type

SELECT jsonData ->> '$.user_name' json_unquote FROM json;

JSON_KEYS(json_doc [, path]) extracts the key values in JSON as JSON array

SELECT JSON_KEYS(jsonData) jsonKey FROM json;

SELECT JSON_KEYS(jsonData -> '$[0]') jsonKey2 FROM json;

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
Query the paths containing the specified string and return it as a json array. If any parameter is NUL or path does not exist, NULL is returned.
one_or_all: "one" means to return when one is found; "All" means to query all.
search_str: the string to query. You can use '%' or '%' in LIKE Match.
Path: check under the specified path.

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

SELECT JSON_SEARCH(@j, 'all', '10') jsonSearch;

SELECT JSON_SEARCH(jsonData, 'one', '23') as jsonSearch FROM json;

3, Functions that modify JSON values
JSON_APPEND(json_doc, path, val [, path, val]...) appends the value to the end of the specified array in the JSON document and returns the result
json_append was abandoned and MySQL 5.7.9 was renamed json_array_append

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
Add an array element at the end. If the original value is a numeric value or json object, turn it into an array and then add the element

SELECT JSON_ARRAY_APPEND(jsonData, '$[0]', '1') as newJson  FROM json  where id = 1;

SELECT JSON_ARRAY_APPEND(jsonData, '$[1]', '1') as newJson  FROM json  where id = 1;

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
Inserts a character at the specified index and returns the corresponding character

SELECT JSON_ARRAY_INSERT(jsonData, '$[0]', '2') jsonArray FROM json where id = 1;

JSON_INSERT(json_doc, path, val[, path, val] ...)
Insert value (inserts a new value, but does not replace an old value that already exists)

SELECT JSON_INSERT(jsonData,'$[1].a','996','$[10]','mysql-json') jsonInsert FROM json where id = 1;

JSON_MERGE(json_doc, json_doc[, json_doc] ...)
Merge json arrays or objects

SELECT JSON_MERGE('[1, 2]', '[true, false]');

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

SELECT JSON_REMOVE(jsonData,'$[0]') as jsonRemove FROM json where id = 1;

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

SELECT JSON_REPLACE(jsonData, '$[1].a', '{1,2,3}') FROM json where id =1;

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

SELECT JSON_SET(jsonData,'$.sex','male','$[100]','json') jsonSet FROM json where id = 2;
JSON_SET()， JSON_INSERT()and JSON_REPLACE()Functional relationship:
JSON_SET() Replace the existing value and add a value that does not exist.
JSON_INSERT() Inserts a value without replacing the existing value.
JSON_REPLACE()Replace existing values only.

4, Function that returns JSON value property
JSON_DEPTH(json_doc) returns the maximum depth of the JSON document

SELECT JSON_DEPTH(jsonData) from json;

JSON_LENGTH(json_doc [, path]) returns the length of the JSON document

SELECT JSON_LENGTH(jsondata,'$[1].b') json_length FROM json where id = 1;

JSON_TYPE(json_val)
Returns a string of JSON value type. This can be an object, array, or scalar type

SELECT jsonData,JSON_TYPE(jsonData) FROM json;

SELECT JSON_TYPE(jsonData -> '$.age') FROM json where id = 2;

json_valid determines whether it is a legal JSON document