MySQL Common JSON functions

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