MySQL中常用的Json函数

最新推荐文章于 2023-08-25 11:24:12 发布
最新推荐文章于 2023-08-25 11:24:12 发布
阅读量242 收藏 1
点赞数
分类专栏： mysql 文章标签： mysql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_37605197/article/details/114882799
版权

官方文档：JSON Functions

NameDescription
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 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. 概述

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()函数查看类型）。优先级从高到低如下：

?
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

　　

3. 常用函数

3.1 创建函数

3.1.1 JSON_ARRAY

JSON_ARRAY(val1,val2,val3...)

生成一个包含指定元素的json数组。

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

　　

3.1.2 JSON_OBJECT

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

生成一个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数，则抛错。

?
1
2
3
4
5
6
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用"号括起来。

?
1
2
3
4
5
6
7
8
9
10
11
12
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)

?
1
2
3
4
5
6
mysql> select CONVERT ( '{"mail": "amy@gmail.com", "name": "Amy"}' ,JSON);
+ ----------------------------------------------------------+
| CONVERT ( '{"mail": "amy@gmail.com", "name": "Amy"}' ,JSON) |
+ ----------------------------------------------------------+
| { "mail" : "amy@gmail.com" , "name" : "Amy" }                 |
+ ----------------------------------------------------------+

　　

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

　　

 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表示所有的都存在才行。

?
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 |
+ ----------------------------------------+

　

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

 

在MySQL 5.7.9+里可以用"->"替代。

?
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> 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)

　　

在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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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])

获取json文档在指定路径下的所有键值，返回一个json array。如果有参数为NULL或path不存在，则返回NULL。

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

　　

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
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 修改函数

3.3.1 JSON_APPEND/JSON_ARRAY_APPEND

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

在指定path的json array尾部追加val。如果指定path是一个json object，则将其封装成一个json array再追加。如果有参数为NULL，则返回NULL。

?
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
38
39
40
41
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] ...)

 在path指定的json array元素插入val，原位置及以右的元素顺次右移。如果path指定的数据非json array元素，则略过此val；如果指定的元素下标超过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
29
30
31
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] ...)

在指定path下插入数据，如果path已存在，则忽略此val（不存在才插入）。

?
1
2
3
4
5
6
7
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] ...)

替换指定路径的数据，如果某个路径不存在则略过（存在才替换）。如果有参数为NULL，则返回NULL。

?
1
2
3
4
5
6
7
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] ...)

设置指定路径的数据（不管是否存在）。如果有参数为NULL，则返回NULL。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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多个json文档。规则如下：

  • 如果都是json array，则结果自动merge为一个json array；
  • 如果都是json object，则结果自动merge为一个json object；
  • 如果有多种类型，则将非json array的元素封装成json array再按照规则一进行mege。
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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] ...)
移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为NULL，则返回NULL。

?
1
2
3
4
5
6
7
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)

去掉val的引号。如果val为NULL，则返回NULL。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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特性查询

3.4.1 JSON_DEEPTH

 JSON_DEPTH(json_doc)

获取json文档的深度。如果参数为NULL，则返回NULL。

空的json array、json object或标量的深度为1。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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])

获取指定路径下的长度。如果参数为NULL，则返回NULL。　

长度的计算规则：

  • 标量的长度为1；
  • json array的长度为元素的个数；
  • json object的长度为key的个数。
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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)

获取json文档的具体类型。如果参数为NULL，则返回NULL。

 

3.4.4 JSON_VALID

JSON_VALID(val)

判断val是否为有效的json格式，是为1，不是为0。如果参数为NUL，则返回NULL。

?
1
2
3
4
5
6
7
8
9
10
11
12
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 |
+ ---------------------+-----------------------+

　　

　

end

王俊超_
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql5.7中JSON操作函数使用说明
09-09
本文给大家分享的是在mysql5.7中操作json的函数的使用方法以及相关示例，非常的实用，有需要的小伙伴可以参考下
Mysql当中Json相关的函数详解
怪咖@的博客
04-30 1万+
从5.7开始，MySQL开始支持json类型，用于存储JSON数据。提供数据类型的同时也提供了很多关于json的函数供我们使用，本篇文章基本上都来源于官网;
Json append 使用
tianyexing2008的博客
04-21 784
像上面的 response["array"] 它不是一个 nullValue 类型，也不是一个 arrayValue 类型，所以抛出了异常。如上的 response 初始化是一个null 值，然后 append 一个 json 对象 config，其结果就是 response 变成了一个数组，其数组 size() 为 1。那上面讲的都是 nullValue 类型情况下的 append()，那如果调用者已经是 arrayValue 的情况下是什么样的呢？运行时错误了，这里表明调用的对象需要是数组类型。
Mysql 中关于修改JSON值的相关函数官方说明
08-20 426
Functions That Modify JSON Values The functions in this section modify JSON values and return the result. JSON_APPEND(json_doc,path,val[,path,val] ...) Appends values to the end of the indicated arrays within a JSON document and returns the r...
在Oracle中实现MySql中的JSON_ARRAY_APPEND
zxx862309181的博客
10-17 1131
在Oracle使用substr和length实现MySQL的JSON_ARRAY_APPEND
mysql处理json格式的字段，一文搞懂mysql解析json数据
秃了也弱了
08-25 9949
JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前，只能通过字符类型（CHAR，VARCHAR 或 TEXT ）来保存 JSON 文档。 MySQL 8.0版本中增加了对JSON类型的索引支持。可以使用CREATE INDEX语句创建JSON类型的索引，提高JSON类型数据的查询效率。 存储JSON文档所需的空间与存储LONGBLOB或LONGTEXT所需的空间大致相同。 在MySQL 8.0.13之前，JSON列不能有非空的默认值。 JSON 类型比较适合存储一些列不固定、修改较少
详解Mysql中的JSON系列操作函数
09-10
新版 Mysql 中加入了对 JSON Document 的支持，可以创建 JSON 类型的字段，并有一套函数支持对JSON的查询、修改等操作，下面就实际体验一下
Mysql Json 检索函数 原代码
07-31
自宝义函数json_searchvalue，从给定的JSON值中，根据给定的Key，检索出需要Value,
java中json的使用
shuixian
01-18 139
json串示例 [{"id":1,"name":"name1","password":"password1","remark":"remark1"},{"id":2,"name":"name2","password":"password2","remark&q
mysql8.0 json操作 JSON_ARRAY_APPEND JSON_REMOVE
qq_29730815的博客
08-08 1544
JSON_SEARCH JSON_UNQUOTE JSON_REMOVE JSON_ARRAY_APPEND Mysql 8 中的 Json Array 中删除 Json 对象。列数据类型为 Json
MySQL处理Json数据
只是甲的博客
11-08 4114
mysql 处理json数据
12.18.4 修改JSON值的函数
repo的博客
03-16 636
MySQL修改JSON值的函数。原文链接：https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html。
mysql的json函数
qq_39726798的博客
11-03 6875
mysql5.7以上提供了一种新的字段格式json，大概是mysql想把非关系型和关系型数据库一口通吃，所以推出了这种非常好用的格式，这样，我们的很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。当然了，5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0解决了。今天我们就针对mysql的json数据格式操作做一个简单的介绍 JSON 数组包含由逗号分隔并包含在[和] 字符中的值列表： ["abc", 10, null, true, fal
mysql定义json字符串数组_Mysql中的JSON系列操作函数
weixin_34683262的博客
01-19 1533
一、方法罗列：分类 函数 描述创建jsonjson_array 创建json数组json_object 创建json对象json_quote 将json转成json字符串类型查询jsonjson_contains 判断是否包含某个json值json_contains_path 判断某个路径下是否包json值json_extract 提取json值column->path jso...
json append
头在云端,脚踏实地
01-19 1493
    今天在处理一个提交的时候需要用js在已有的json的对象中再增加一对值,请教了同事,处理如下:       jsonData['addKey']=addValue;      简洁明了,呵呵。...
MYSQL JSON 函数
程小明的博客
07-01 8295
MYSQL JSON函数 json API
MySQL5.7中对JSON的支持
莫忘
12-16 2万+
概述从MySQL5.7.8开始，Mysql提供了一个原生的Json类型，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式。在Json列插入或者更新的时候将会自动验证Json文本，未通过验证的文本将产生一个错误信息。Json文本采用标准的创建方式，可以使用大多数的比较操作符进行比较操作
MySQL 5.7新增对JSON支持
热门推荐
格子的博客
09-19 14万+
最近加入一个新的项目组，在看项目代码的时候发现很多地方使用的一些操作从来没用过的，百思不能其解这是什么操作。 百度了下发现在是Mysql5.7版本以后新增的功能，Mysql提供了一个原生的Json类型，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式。在Json列插入或者更新的时候将会...
mysql5.7 json函数
最新发布
09-09
MySQL 5.7引入了一系列的JSON函数，用于处理和操作JSON格式的数据。这些函数可以分为几个不同的分类，包括函数创建JSON值、查询JSON值和修改JSON值。 在函数创建JSON值的分类中，有一些常用的函数可以用来创建JSON...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值