MySQL 常⽤Json 函数
写的很棒！
官⽅⽂档：
Name
Description
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()).merge函数
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
2. ⽐较规则
json 中的数据可以⽤ =, <, <=, >, >=, <>, !=, and <=> 进⾏⽐较。但json ⾥的数据类型可以是多样的，那么在不同类型之间进⾏⽐较时，就有优先级了，⾼优先级的要⼤于低优先级的（可以⽤JSON_TYPE()函数查看类型）。优先级从⾼到低如下：
3. 常⽤函数
3.1 创建函数
3.1.1 JSON_ARRAY
JSON_ARRAY(val1,)1
2
3
4
5
6
7
8
9
10
11
12
BLOB BIT OPAQUE DATETIME TIME DATE BOOLEAN ARRAY OBJECT STRING INTEGER, DOUBLE NULL
⽣成⼀个包含指定元素的json 数组。
3.1.2 JSON_OBJECT
JSON_OBJECT(key1,val1,)
⽣成⼀个包含指定K-V 对的json object 。如果有key 为NULL 或参数个数为奇数，则抛错。
3.1.3 JSON_QUOTE
JSON_QUOTE(json_val)
将json_val ⽤"号括起来。
3.1.4 CONVERT
CONVERT(json_string,JSON)
3.2 查询函数
3.2.1 JSON_CONTAINS
JSON_CONTAINS(json_doc, val[, path])
查询json ⽂档是否在指定path 包含指定的数据，包含则返回1，否则返回0。如果有参数为NULL 或path 不存在，则返回NULL 。1
2
3
4
5
6
mysql>SELECT JSON_ARRAY(1,"abc",NULL ,TRUE , CURTIME());+---------------------------------------------+| JSON_ARRAY(1,
"abc",NULL ,TRUE , CURTIME()) |+---------------------------------------------+| [1,"abc",null ,true ,"11:30:24.000000"] |+---------------------------------------------+1
2
3
4
5
6
mysql>SELECT JSON_OBJECT('id', 87,'name','carrot');+-----------------------------------------+| JSON_OBJECT(
'id', 87,'name','carrot') |+-----------------------------------------+| {"id": 87,"name":"carrot"} |+-----------------------------------------+1
2
3
4
5
6
7
8
9
10
11
12
mysql>SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');+--------------------+----------------------+| JS
ON_QUOTE('null') | JSON_QUOTE('"null"') |+--------------------+----------------------+|"null" |"\"null\"" |+--------------------+----------------------+mysql>SELECT JSON_QUOTE('[1, 2, 3]');+-------------------------+| JSON_QUOTE(
'[1, 2, 3]') |+-------------------------+|"[1, 2, 3]" |+-------------------------+1
2
3
4
5
6
mysql>select CONVERT ('{"mail": "amy@gmail", "name": "Amy"}',JSON);+----------------------------------------------------------+|CONVERT ('{"mail": "amy@gmail", "name": "Amy"}',JSON) |+----------------------------------------------------------+| {"mail":"amy@gmail","name":"Amy"} |+----------------------------------------------------------+
3.2.2 JSON_CONTAINS_PATH
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
查询是否存在指定路径，存在则返回1，否则返回0。如果有参数为NULL ，则返回NULL 。
one_or_all 只能取值"one"或"all"，one 表⽰只要有⼀个存在即可；all 表⽰所有的都存在才⾏。
3.2.3 JSON_EXTRACT
JSON_EXTRACT(json_doc, path[, path] ...)
从json ⽂档⾥抽取数据。如果有参数有NULL 或path 不存在，则返回NULL 。如果抽取出多个path ，则返回的数据封闭在⼀个json array ⾥。 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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 |
+-------------------------------+1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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 |
+----------------------------------------+
在MySQL 5.7.9+⾥可以⽤"->"替代。
在MySQL 5.7.13+，还可以⽤"->>"表⽰去掉抽取结果的"号，下⾯三种效果是⼀样的：JSON_UNQUOTE( JSON_EXTRACT(column, path) )JSON_UNQUOTE(column -> path)
column->>path 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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] |
+-----------------------------------------------+1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25mysql>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 |+-------------------------------+-----------+------+3rows 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 |+
-------------------------------+-----------+------+3rows in set (0.00 sec)
3.2.4 JSON_KEYS
JSON_KEYS(json_doc[, path])
获取json ⽂档在指定路径下的所有键值，返回⼀个json array 。如果有参数为NULL 或path 不存在，则返回NULL 。
3.2.5 JSON_SEARCH
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
查询包含指定字符串的paths ，并作为⼀个json array 返回。如果有参数为NUL 或path 不存在，则返回NULL 。one_or_all ："one"表⽰查询到⼀个即返回；"all"表⽰查询所有。
search_str ：要查询的字符串。 可以⽤LIKE ⾥的'%'或‘_’匹配。
path ：在指定path 下查。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38mysql>SELECT *FROM jemp WHERE g > 2;+-------------------------------+------+| c | g |+-------------------------------+------+| {"id":"3","name":"Barney"} | 3 || {"id":"4","name":"Betty"} | 4 |+-------------------------------+------+2rows in set (0.01 sec) mysql>SELECT c->'$.name' AS name -> FROM jemp WHERE g > 2;+----------+|name |+----------+|"Barney" ||"Betty" |+----------+2rows in set (0.00 sec) mysql>SELECT JSON_UNQUOTE(c->'$.name')AS name -> FROM jemp WHERE g > 2;+--------+|name |+--------+| Barney || Betty |+--------+2rows in set (0.00 sec) mysql>SELECT c->>'$.name' AS name -> FROM jemp WHERE g > 2;+--------+|name |+--------+| Barney || Betty |+
--------+2rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
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"] |+----------------------------------------------+1
2
3mysql>SET @j ='["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]'; mysql>SELECT JSON_SEARCH(@j,'one','abc');
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论