How to use MySQL common functions to process JSON

How to use MySQL common functions to process JSON

Official documentation: JSON Functions

Name Description
JSON_APPEND() Append data to JSON document
JSON_ARRAY() Create JSON array
JSON_ARRAY_APPEND() Append data to JSON document
JSON_ARRAY_INSERT() Insert into JSON array
-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS() Whether a JSON document contains a specific object at path
JSON_CONTAINS_PATH() Whether JSON document contains any data at path
JSON_DEPTH() Maximum depth of JSON document
JSON_EXTRACT() Return data from JSON document
->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT() Insert data into JSON document
JSON_KEYS() Array of keys from JSON document
JSON_LENGTH() Number of elements in JSON document
JSON_MERGE() Merge JSON documents
JSON_OBJECT() Create JSON object
JSON_QUOTE() Quote JSON document
JSON_REMOVE() Remove data from JSON document
JSON_REPLACE() Replace values ​​in JSON document
JSON_SEARCH() Path to value within JSON document
JSON_SET() Insert data into JSON document
JSON_TYPE() Type of JSON value
JSON_UNQUOTE() Unquote JSON value
JSON_VALID() Whether JSON value is valid

1. Overview

The json in MySQL is divided into json array and json object. $ represents the entire JSON object. When indexing data, use the subscript (for JSON array, start from 0) or the key value (for JSON object, keys containing special characters should be enclosed in ", such as $."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

$[1].a：[5, 6]

$[1].a[1]：6

$[1].b：10

$[2][0]：99

2. Comparison rules

Data in JSON can be compared using =, <, <=, >, >=, <>, !=, and <=>. But the data types in json can be diverse, so when comparing different types, there is a priority, and高優先級的要大于低優先級的(you can use the JSON_TYPE() function to check the type). The priorities from high to low are as follows:

BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

3. Common functions

3.1 Creating a function

3.1.1 JSON_ARRAY

JSON_ARRAY(val1,val2,val3...)

Generates a json array containing the specified elements.

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------+
| [1, "abc", null, true, "11:30:24.000000"] |
+---------------------------------------------+

3.1.2 JSON_OBJECT

JSON_OBJECT(key1,val1,key2,val2...)

Generates a json object containing the specified KV pair. If any key is NULL or the number of parameters is odd, an error is thrown.

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"} |
+-----------------------------------------+

3.1.3 JSON_QUOTE

JSON_QUOTE(json_val)

Enclose json_val in ".

mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
+--------------------+----------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"null"') |
+--------------------+----------------------+
| "null" | "\"null\"" |
+--------------------+----------------------+
mysql> SELECT JSON_QUOTE('[1, 2, 3]');
+-------------------------+
| JSON_QUOTE('[1, 2, 3]') |
+-------------------------+
| "[1, 2, 3]" |
+-------------------------+

3.1.4 CONVERT

CONVERT(json_string,JSON)

mysql> select CONVERT('{"mail": "[email protected]", "name": "Amy"}',JSON);
+----------------------------------------------------------+
| CONVERT('{"mail": "[email protected]", "name": "Amy"}',JSON) |
+----------------------------------------------------------+
| {"mail": "[email protected]", "name": "Amy"} |
+----------------------------------------------------------+

3.2 Query Function

3.2.1 JSON_CONTAINS

JSON_CONTAINS(json_doc, val[, path])

Query whether the json document contains the specified data in the specified path. If yes, it returns 1; otherwise, it returns 0. If any parameter is NULL or path does not exist, NULL is returned.

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 1 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.b') |
+-------------------------------+
| 0 |
+-------------------------------+
 
mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 0 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.c') |
+-------------------------------+
| 1 |
+-------------------------------+

3.2.2 JSON_CONTAINS_PATH

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

Checks whether the specified path exists. If it exists, it returns 1; otherwise, it returns 0. If any argument is NULL, returns NULL.

one_or_all can only take the value "one" or "all". One means that only one of them needs to exist; all means that all of them must exist.

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
| 0 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
| 1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
| 0 |
+----------------------------------------+

3.2.3 JSON_EXTRACT

JSON_EXTRACT(json_doc, path[, path] ...)

Extract data from json document. If any parameter is NULL or path does not exist, NULL is returned. If multiple paths are extracted, the returned data is enclosed in a JSON array.

mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 20 |
+--------------------------------------------+
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
+----------------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
+----------------------------------------------------+
| [20, 10] |
+----------------------------------------------------+
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
+-----------------------------------------------+
| [30, 40] |
+-----------------------------------------------+

In MySQL 5.7.9+, you can use "->" instead.

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
   > FROM jemp
   > WHERE JSON_EXTRACT(c, "$.id") > 1
   > ORDER BY JSON_EXTRACT(c, "$.name");
+-------------------------------+-----------+------+
| c | c->"$.id" | g |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3" | 3 |
| {"id": "4", "name": "Betty"} | "4" | 4 |
| {"id": "2", "name": "Wilma"} | "2" | 2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)
 
mysql> SELECT c, c->"$.id", g
   > FROM jemp
   > WHERE c->"$.id" > 1
   > ORDER BY c->"$.name";
+-------------------------------+-----------+------+
| c | c->"$.id" | g |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3" | 3 |
| {"id": "4", "name": "Betty"} | "4" | 4 |
| {"id": "2", "name": "Wilma"} | "2" | 2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)

In MySQL 5.7.13+, you can also use "->>" to remove the " sign from the extracted result. The following three effects are the same:

  • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
  • JSON_UNQUOTE(column -> path)
  • column->>path
mysql> SELECT * FROM jemp WHERE g > 2;
+-------------------------------+------+
| c | g |
+-------------------------------+------+
| {"id": "3", "name": "Barney"} | 3 |
| {"id": "4", "name": "Betty"} | 4 |
+-------------------------------+------+
2 rows in set (0.01 sec)
 
mysql> SELECT c->'$.name' AS name  
  -> FROM jemp WHERE g > 2;
+----------+
| name |
+----------+
| "Barney" |
| "Betty" |
+----------+
2 rows in set (0.00 sec)
 
mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
  -> FROM jemp WHERE g > 2;
+--------+
| name |
+--------+
| Barney |
| Betty |
+--------+
2 rows in set (0.00 sec)
 
mysql> SELECT c->>'$.name' AS name
  -> FROM jemp WHERE g > 2;
+--------+
| name |
+--------+
| Barney |
| Betty |
+--------+
2 rows in set (0.00 sec)

3.2.4 JSON_KEYS

JSON_KEYS(json_doc[, path])

Get all key values ​​of the json document under the specified path and return a json array. If any parameter is NULL or path does not exist, NULL is returned.

mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"] |
+---------------------------------------+
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"] |
+----------------------------------------------+

3.2.5 JSON_SEARCH

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

Query paths that contain the specified string and return them as a JSON array. If any argument is NUL or path does not exist, NULL is returned.

  • one_or_all: "one" means returning all results if one is found; "all" means returning all results.
  • search_str: The string to search for. You can use '%' or '_' in LIKE to match.
  • path: Search under the specified path.
mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
 
mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]" |
+-------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"] |
+-------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'ghi') |
+-------------------------------+
| NULL |
+-------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '10');
+------------------------------+
| JSON_SEARCH(@j, 'all', '10') |
+------------------------------+
| "$[1][0].k" |
+------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
+-----------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$') |
+-----------------------------------------+
| "$[1][0].k" |
+-----------------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
+--------------------------------------------+
| "$[1][0].k" |
+--------------------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
+---------------------------------------------+
| "$[1][0].k" |
+---------------------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
+-------------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
+-------------------------------------------------+
| "$[1][0].k" |
+-------------------------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
+--------------------------------------------+
| "$[1][0].k" |
+--------------------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
+-----------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
+-----------------------------------------------+
| "$[1][0].k" |
+-----------------------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x" |
+---------------------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%a%') |
+-------------------------------+
| ["$[0]", "$[2].x"] |
+-------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%b%') |
+-------------------------------+
| ["$[0]", "$[2].x", "$[3].y"] |
+-------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
+---------------------------------------------+
| "$[0]" |
+---------------------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x" |
+---------------------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
+---------------------------------------------+
| NULL |
+---------------------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
 
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
+-------------------------------------------+
| "$[3].y" |
+-------------------------------------------+

3.3 Modifying functions

3.3.1 JSON_APPEND/JSON_ARRAY_APPEND

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

Append val to the end of the json array at the specified path. If the specified path is a JSON object, it will be encapsulated into a JSON array and then appended. If any argument is NULL, returns NULL.

mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"] |
+----------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[0]', 2) |
+----------------------------------+
| [["a", 2], ["b", "c"], "d"] |
+----------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
+-------------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
+-------------------------------------+
| ["a", [["b", 3], "c"], "d"] |
+-------------------------------------+
 
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} |
+------------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
+--------------------------------------+
| JSON_ARRAY_APPEND(@j, '$.c', 'y') |
+--------------------------------------+
| {"a": 1, "b": [2, 3], "c": [4, "y"]} |
+--------------------------------------+
 
mysql> SET @j = '{"a": 1}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
+---------------------------------+
| JSON_ARRAY_APPEND(@j, '$', 'z') |
+---------------------------------+
| [{"a": 1}, "z"] |
+---------------------------------+

3.3.2 JSON_ARRAY_INSERT

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

Insert val into the json array element specified by path, and shift the original position and the elements to the right right sequentially. If the data specified by path is not a JSON array element, this val is skipped; if the specified element index exceeds the length of the JSON array, it is inserted at the end.

mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
+------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]] |
+------------------------------------+
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
+--------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[100]', 'x') |
+--------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4], "x"] |
+--------------------------------------+
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]] |
+-----------------------------------------+
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');
+---------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') |
+---------------------------------------+
| ["a", {"b": [1, 2]}, [3, "y", 4]] |
+---------------------------------------+
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.3.3 JSON_INSERT/JSON_REPLACE/JSON_SET

JSON_INSERT(json_doc, path, val[, path, val] ...)

Insert data under the specified path. If the path already exists, this val is ignored (it will be inserted only if it does not exist).

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]"} |
+----------------------------------------------------+

JSON_REPLACE(json_doc, path, val[, path, val] ...)

Replace the data in the specified path. If a path does not exist, it will be skipped (replace it only if it exists). If any argument is NULL, returns NULL.

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]} |
+-----------------------------------------------------+

JSON_SET(json_doc, path, val[, path, val] ...)

Sets the data for the specified path (whether it exists or not). If any argument is NULL, returns NULL.

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
+-------------------------------------------------+
| JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
+-------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"} |
+-------------------------------------------------+
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]"} |
+----------------------------------------------------+
mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------+
| JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 3]} |
+-----------------------------------------------------+

3.3.4 JSON_MERGE

JSON_MERGE(json_doc, json_doc[, json_doc] ...)

Merge multiple JSON documents. The rules are as follows:

  • If they are all json arrays, the results are automatically merged into a json array;
  • If they are all json objects, the results are automatically merged into one json object;
  • If there are multiple types, encapsulate the non-JSON array elements into a JSON array and then mege according to rule one.
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('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}] |
+------------------------------------+

3.3.5 JSON_REMOVE

JSON_REMOVE(json_doc, path[, path] ...)

Remove the data of the specified path. If a path does not exist, it will be skipped. If any argument is NULL, returns NULL.

mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_REMOVE(@j, '$[1]');
+-------------------------+
| JSON_REMOVE(@j, '$[1]') |
+-------------------------+
| ["a", "d"] |
+-------------------------+

3.3.6 JSON_UNQUOTE

JSON_UNQUOTE(val)

Remove the quotes around val. If val is NULL, returns NULL.

mysql> SET @j = '"abc"';
mysql> SELECT @j, JSON_UNQUOTE(@j);
+-------+------------------+
| @j | JSON_UNQUOTE(@j) |
+-------+------------------+
| "abc" | abc |
+-------+------------------+
mysql> SET @j = '[1, 2, 3]';
mysql> SELECT @j, JSON_UNQUOTE(@j);
+-----------+------------------+
| @j | JSON_UNQUOTE(@j) |
+-----------+------------------+
| [1, 2, 3] | [1, 2, 3] |
+-----------+------------------+

3.4 JSON Feature Query

3.4.1 JSON_DEEPTH

JSON_DEPTH(json_doc)

Get the depth of a json document. If the argument is NULL, returns NULL.

An empty JSON array, JSON object, or scalar has a depth of 1.

mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
+------------------+------------------+--------------------+
| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
+------------------+------------------+--------------------+
| 1 | 1 | 1 |
+------------------+------------------+--------------------+
mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
+------------------------+------------------------+
| JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') |
+------------------------+------------------------+
| 2 | 2 |
+------------------------+------------------------+
mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');
+-------------------------------+
| JSON_DEPTH('[10, {"a": 20}]') |
+-------------------------------+
| 3 |
+-------------------------------+

3.4.2 JSON_LENGTH

JSON_LENGTH(json_doc[, path])

Get the length of the specified path. If the argument is NULL, returns NULL.

Length calculation rules:

  • The length of a scalar is 1;
  • The length of the json array is the number of elements;
  • The length of the json object is the number of keys.
mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
+---------------------------------+
| JSON_LENGTH('[1, 2, {"a": 3}]') |
+---------------------------------+
| 3 |
+---------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
| 2 |
+-----------------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
+------------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
+------------------------------------------------+
| 1 |
+------------------------------------------------+

3.4.3 JSON_TYPE

JSON_TYPE(json_val)

Get the specific type of the json document. If the argument is NULL, returns NULL.

3.4.4 JSON_VALID

JSON_VALID(val)

Determine whether val is in a valid JSON format, if it is 1, otherwise it is 0. If the argument is NUL, NULL is returned.

mysql> SELECT JSON_VALID('{"a": 1}');
+------------------------+
| JSON_VALID('{"a": 1}') |
+------------------------+
| 1 |
+------------------------+
mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
+---------------------+-----------------------+
| JSON_VALID('hello') | JSON_VALID('"hello"') |
+---------------------+-----------------------+
| 0 | 1 |
+---------------------+-----------------------+

This is the end of this article about the use of common MySQL JSON functions. For more related MySQL JSON common functions, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • How to map the fields in MySQL to JSON format in mybatis plus entity class
  • MySQL extracts Json internal fields and dumps them as numbers
  • Example code for converting Mysql query result set into JSON data
  • Detailed explanation of querying JSON format fields in MySQL
  • MySQL json format data query operation
  • MySQL 8.0 can now handle JSON

<<:  Parsing the commonly used v-instructions in vue.js

>>:  WML tag summary

Recommend

Introduction to the Enctype attribute of the Form tag and its application examples

Enctype : Specifies the type of encoding the brows...

How to clear mysql registry

Specific method: 1. Press [ win+r ] to open the r...

JavaScript uses promise to handle multiple repeated requests

1. Why write this article? You must have read a l...

Div css naming standards css class naming rules (in line with SEO standards)

There are many tasks to be done in search engine o...

Master-slave synchronization configuration of Mysql database

Table of contents Mysql master-slave synchronizat...

Detailed explanation of the correct use of the count function in MySQL

1. Description In MySQL, when we need to get the ...

JavaScript implementation of carousel example

This article shares the specific code for JavaScr...

HTML+CSS+JS sample code to imitate the brightness adjustment effect of win10

HTML+CSS+JS imitates win10 brightness adjustment ...

Install tomcat and deploy the website under Linux (recommended)

Install jdk: Oracle official download https://www...

The basic principles and detailed usage of viewport

1. Overview of viewport Mobile browsers usually r...

Let's talk briefly about the changes in setup in vue3.0 sfc

Table of contents Preface Standard sfc writing me...

WeChat applet component development: Visual movie seat selection function

Table of contents 1. Introduction 1. Component da...

The linkage method between menu and tab of vue+iview

Vue+iview menu and tab linkage I am currently dev...

Automatically install the Linux system based on cobbler

1. Install components yum install epel-rpm-macros...

Vue Element front-end application development to obtain back-end data

Table of contents Overview 1. Acquisition and pro...