Home >Database >Mysql Tutorial >MySQL 5.7 new features | Json Column and Generated Column (medium)
First title
1. Append of JSON_ARRAY_APPEND array class
This usage is mainly to process the value array type data in the JSON field, mainly to append the value, which is not a standard array type. This function will automatically convert to array form, use the previous value as the first element of the array and then append it. There is a batch append method:
mysql> SET @j = '["a", ["b", "c"], "d"]';
Append to the array of the second value in the field An element 1:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1); +----------------------------------+ | JSON_ARRAY_APPEND(@j, '$[1]', 1) | +----------------------------------+ | ["a", ["b", "c", 1], "d"] | +----------------------------------+
Appends an element 2 to the array of the first value in the field. If it is not a single value in the form of an array, it will be changed to an array and appended:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2); +----------------------------------+ | JSON_ARRAY_APPEND(@j, '$[0]', 2) | +----------------------------------+ | [["a", 2], ["b", "c"], "d"] | +----------------------------------+
Appends an element 3 to the first array of the second value in the field. If it is not a single value in the form of an array, it changes it to an array and appends:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3); +-------------------------------------+ | JSON_ARRAY_APPEND(@j, '$[1][0]', 3) | +-------------------------------------+ | ["a", [["b", 3], "c"], "d"] | +-------------------------------------+
In the field Append an element "x" to the value array with key "b":
mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}'; mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x'); +------------------------------------+ | JSON_ARRAY_APPEND(@j, '$.b', 'x') | +------------------------------------+ | {"a": 1, "b": [2, 3, "x"], "c": 4} | +------------------------------------+
In the value array with key "c" in the field Append an element "y":
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y'); +--------------------------------------+ | JSON_ARRAY_APPEND(@j, '$.c', 'y') | +--------------------------------------+ | {"a": 1, "b": [2, 3], "c": [4, "y"]} | +--------------------------------------+
If the JSON field is a standard JSON type, using this function will change it into an array form and append the value:
mysql> SET @j = '{"a": 1}'; mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z'); +---------------------------------+ | JSON_ARRAY_APPEND(@j, '$', 'z') | +---------------------------------+ | [{"a": 1}, "z"] | +---------------------------------+
If the JSON field is a standard JSON type, batch append:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z','$', 'w'); +---------------------------------+ | JSON_ARRAY_APPEND(@j, '$', 'z') | +---------------------------------+ | [{"a": 1}, "z", "w"] | +---------------------------------+
2. Insertion of JSON_ARRAY_INSERT array class
This usage is mainly to process the value array type data in the JSON field. Mainly for insertion, it is not a standard array type. This function will automatically convert to array form, use the previous value as the first element of the array and then append:
mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
Insert characters after the first value of the array 'x' element:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x'); +------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[1]', 'x') | +------------------------------------+ | ["a", "x", {"b": [1, 2]}, [3, 4]] |
Insert the character 'x' before the first value in the array. Element:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x'); +------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[1]', 'x') | +------------------------------------+ | ["x","a", {"b": [1, 2]}, [3, 4]] | +------------------------------------+
In the array Insert the character 'x' element after the 100th value. If it is too long, insert it after the last value:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x'); +--------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[100]', 'x') | +--------------------------------------+ | ["a", {"b": [1, 2]}, [3, 4], "x"] | +--------------------------------------+
In the first value of the array, the value array with key 'b' Enter the character element 'x' before 1 value:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x'); +-----------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') | +-----------------------------------------+ | ["a", {"b": ["x", 1, 2]}, [3, 4]] | +-----------------------------------------+
Enter the character element 'y' after the second value in the array and after the first value in the array:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y'); +---------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') | +---------------------------------------+ | ["a", {"b": [1, 2]}, [3, "y", 4]] | +---------------------------------------+
Batch insertion of array elements:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y'); +----------------------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') | +----------------------------------------------------+ | ["x", "a", {"b": [1, 2]}, [3, 4]] | +----------------------------------------------------+
3. JSON_INSERT standard JSON value insertion
If the key already exists, the old value will not be overwritten. Insertion of non-existent values:
mysql> SET @j = '{ "a": 1, "b": [2, 3]}'; mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]'); +----------------------------------------------------+ | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') | +----------------------------------------------------+ | {"a": 1, "b": [2, 3], "c": "[true, false]"} | +----------------------------------------------------+
4. JSON_MERGE merge operation
JSON_MERGE is mainly used for merging: if two are arrays, merge into one array; if two are JSON, merge into one JSON , if two JSONs have the same key, the values are merged into an array; if they are two single values, they are merged into an array; if they are an array and one is JSON, they are merged into an array:
mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]'); +---------------------------------------+ | JSON_MERGE('[1, 2]', '[true, false]') | +---------------------------------------+ | [1, 2, true, false] | +---------------------------------------+ mysql> SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}'); +-------------------------------------------+ | JSON_MERGE('{"name": "x"}', '{"id": 47}') | +-------------------------------------------+ | {"id": 47, "name": "x"} | +-------------------------------------------+ mysql> SELECT JSON_MERGE('{"id": "x"}', '{"id": 47}'); +-------------------------------------------+ | JSON_MERGE('{"id": "x"}', '{"id": 47}') | +-------------------------------------------+ | {"id": ["x",47]} | +-------------------------------------------+ mysql> SELECT JSON_MERGE('1', 'true'); +-------------------------+ | JSON_MERGE('1', 'true') | +-------------------------+ | [1, true] | +-------------------------+ mysql> SELECT JSON_MERGE('[1, 2]', '{"id": 47}'); +------------------------------------+ | JSON_MERGE('[1, 2]', '{"id": 47}') | +------------------------------------+ | [1, 2, {"id": 47}] | +------------------------------------+
5. JSON_REMOVE deletion operation
JSON_REMOVE deletion operation. This is relatively simple. It can delete a certain value in the field, either in array or JSON format:
mysql> SET @j = '["a", ["b", "c"], "d"]'; mysql> SELECT JSON_REMOVE(@j, '$[1]'); +-------------------------+ | JSON_REMOVE(@j, '$[1]') | +-------------------------+ | ["a", "d"] | +-------------------------+ mysql> SET @j = '{"name": "x","id": 47}'; mysql> SELECT JSON_REMOVE(@j, '$.id'); +-------------------------+ | JSON_REMOVE(@j, '$.id') | +-------------------------+ | {"name": "x"} | +-------------------------+ mysql> SELECT JSON_REMOVE(@j, '$.id', '$.name'); +-----------------------------------+ | JSON_REMOVE(@j, '$.id', '$.name') | +-----------------------------------+ | {} | +-----------------------------------+ mysql> SET @j = '{"name": "x","id": [47,48]}'; mysql> SELECT JSON_REMOVE(@j, '$.id[1]'); +-------------------------+ | JSON_REMOVE(@j, '$.id') | +-------------------------+ | {"id": [47],"name": "x"} | +-------------------------+
6. JSON_REPLACE operation
JSON_REPLACE replacement operation, if there is any, replace it, if not, ignore it.
mysql> SET @j = '{ "a": 1, "b": [2, 3]}'; mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]'); +-----------------------------------------------------+ | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') | +-----------------------------------------------------+ | {"a": 10, "b": [2, 3]} +-----------------------------------------------------+ mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.b[1]',99); +-----------------------------------------------------+ | JSON_REPLACE(@j, '$.a', 10, '$.b[1]',99') | +-----------------------------------------------------+ | {"a": 10, "b": [2, 99]} | +-----------------------------------------------------+
7. JSON_SET operation
JSON_SET operation is an update and insert operation. If it already exists, update it; if it does not exist, insert it. The following are the differences in the execution results of JSON_SET, JSON_INSERT, and JSON_REPLACE:
The above is the content of MySQL 5.7 new features | Json Column and Generated Column (middle). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!