标签:NameDescription
Append data to JSON document
Create JSON array
Append data to JSON document
Insert into JSON array
Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
Whether JSON document contains specific object at path
Whether JSON document contains any data at path
Maximum depth of JSON document
Return data from JSON document
Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
Insert data into JSON document
Array of keys from JSON document
Number of elements in JSON document
Merge JSON documents
Create JSON object
Quote JSON document
Remove data from JSON document
Replace values in JSON document
Path to value within JSON document
Insert data into JSON document
Type of JSON value
Unquote JSON value
Whether JSON value is valid
1. 概述
MySQL里的json分为json array和json object。 $表示整个json对象,在索引数据时用下标(对于json array,从0开始)或键值(对于json object,含有特殊字符的key要用"括起来,比如$."my name")。
例如:[3, {"a": [5, 6], "b": 10}, [99, 100]],那么:
$[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
二、比较规则
json中的数据可以用 =, , >=, <>, !=, and <=> 进行比较。但json里的数据类型可以是多样的,那么在不同类型之间进行比较时,就有优先级了,高优先级的要大于低优先级的(可以用JSON_TYPE()函数查看类型)。优先级从高到低如下:
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL
三、常用函数
3.1 创建函数
3.1.1 JSON_ARRAY
JSON_ARRAY(val1,val2,val3...)
生成一个包含指定元素的json数组。
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...)
生成一个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数,则抛错。
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)
将json_val用"号括起来。
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": "amy@gmail.com", "name": "Amy"}‘,JSON);
+----------------------------------------------------------+
| CONVERT(‘{"mail": "amy@gmail.com", "name": "Amy"}‘,JSON) |
+----------------------------------------------------------+
| {"mail": "amy@gmail.com", "name": "Amy"} |
+----------------------------------------------------------+
end
标签: