MySQL 5.7 new features | Json Column and Generated Column (medium)
- 黄舟Original
- 2017-02-07 13:11:401236browse
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)!