Official Document: 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 the JSON column after evaluating path; Equivalent to Json_extract (). |
JSON_CONTAINS() |
Whether JSON document contains 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, with subscript (for JSON array, starting from 0), or key value (for JSON object, key with special characters "enclosed, such as $.") in the index data. 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
Ii. Rules of comparison
The data in JSON can be compared with =, <, <=,, >=, <>,! =, and <=>. However, the data types in JSON can be varied, so when comparing between different types, there is a priority, high priority is greater than the low priority (you can use the Json_type () function to view the type). The priority level from high to low is as follows:
Blobbitopaquedatetimetimedatebooleanarrayobjectstringinteger, Doublenull
Iii. 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 that contains the specified k-v pair. If a key is null or the number of arguments is odd, the error is thrown.
mysql> SELECT json_object (' id ', the ' name ', ' carrot '); +-----------------------------------------+| Json_object (' id ', carrot, ' name ', ' |+ ')-----------------------------------------+| {"id": "," "Name": "Carrot"} | +-----------------------------------------+
3.1.3 Json_quote
Json_quote (Json_val)
Enclose the json_val with a "number."
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"} | +----------------------------------------------------------+
End
MySQL Common JSON functions