目录
JSON格式简介
定义用户变量
JSON格式的规范化
JSON的路径表达式
JSON的比较和排序
JSON的函数
一,创建JSON格式数据
1,JSON_ARRAY([val[, val] …])
2,JSON_OBJECT([key, val[, key, val] …])
3,JSON_QUOTE(string)
二,JSON的查询
1,JSON_CONTAINS(target, candidate[, path])
2,JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)
3,JSON_EXTRACT(json_doc, path[, path] …)
4,->运算符
5,->>运算符
6,JSON_KEYS(json_doc[, path])
7,JSON_OVERLAPS(json_doc1, json_doc2)
8,JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])
9,value MEMBER OF(json_array)
三,JSON值的修改
1,JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
2,JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)
3,JSON_INSERT(json_doc, path, val[, path, val] …)
4,JSON_MERGE(json_doc, json_doc[, json_doc] …)
5,JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] …)
6,JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] …)
7,JSON_REMOVE(json_doc, path[, path] …)
8,JSON_REPLACE(json_doc, path, val[, path, val] …)
9,JSON_SET(json_doc, path, val[, path, val] …)
10,JSON_UNQUOTE(json_val)
四,JSON值的属性
1,JSON_DEPTH(json_doc)
2,JSON_LENGTH(json_doc[, path])
3,JSON_TYPE(json_val)
4,JSON_VALID(val)
五,JSON临时表函数
六,JSON格式校验
1,JSON_SCHEMA_VALID(schema,document)
2,JSON_SCHEMA_VALIDATION_REPORT(schema,document)
七,JSON辅助函数
1,JSON_PRETTY(json_val)
2,JSON_STORAGE_FREE(json_val)
3,JSON_STORAGE_SIZE(json_val)
JSON格式简介
MySQL8.0开始支持JSON格式,可以对JSON格式的数据进行高效的访问。
和原来JSON格式的字符串相比,JSON格式有以下的优点:
1,自动验证。错误的JSON格式会报错。
2,存储格式优化。数据保存为二进制格式,文件存储很紧凑,读取速度快。
3,MySQL可以通过键或数组索引查询和修改对应的值,不用把整个字符串都读出来。
一些其他的介绍:
1,JSON格式需要的磁盘空间和longblob或longtext差不多。
2,JSON格式的默认值只能是null。
3,JSON格式的列不能直接建立索引,可以建立JSON索引。
4,JSON格式的key必须是字符串格式。value可以是字符串,数字,布尔型。
5,JSON格式默认使用utf8mb4字符集,utf8mb4-bin排序,其他字符集使用JSON格式需要做字符集转换。ascii或utf8不用转换,他们是utf8mb4的子集。
6,大小写敏感,而且true,false,null这些关键字在JSON格式里都必须小写。说白了就是:null,Null,NULL,都是null,但是"Null"无法转成null,只有"null"才能转成null。
比如:
mysql> SELECT JSON_ARRAY(‘x’) = JSON_ARRAY(‘X’);
±----------------------------------+
| JSON_ARRAY(‘x’) = JSON_ARRAY(‘X’) |
±----------------------------------+
| 0 |
±----------------------------------+
mysql> SELECT JSON_VALID(‘null’), JSON_VALID(‘Null’), JSON_VALID(‘NULL’);
±-------------------±-------------------±-------------------+
| JSON_VALID(‘null’) | JSON_VALID(‘Null’) | JSON_VALID(‘NULL’) |
±-------------------±-------------------±-------------------+
| 1 | 0 | 0 |
±-------------------±-------------------±-------------------+
mysql> SELECT CAST(‘null’ AS JSON);
±---------------------+
| CAST(‘null’ AS JSON) |
±---------------------+
| null |
±---------------------+
1 row in set (0.00 sec)
mysql> SELECT CAST(‘NULL’ AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:
“Invalid value.” at position 0 in ‘NULL’.
mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
±-------------±-------------±-------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
±-------------±-------------±-------------+
| 1 | 1 | 1 |
±-------------±-------------±-------------+
7,JSON格式中包含单引号或双引号时,需要用一条反斜线来转义。
8,JSON格式会丢弃一些额外的空格,并且会把键值对排序。
定义用户变量
使用例子:
mysql> SET @j = JSON_OBJECT(‘key’, ‘value’);
mysql> SELECT @j;
±-----------------+
| @j |
±-----------------+
| {“key”: “value”} |
±-----------------+
注意,定义的用户变量不再是JSON格式,而是String格式,这个是在赋值给变量的时候转的。另外,变量的字符集和排序规则和JSON格式相同:utf8mb4和utf8mb4_bin:
mysql> SELECT CHARSET(@j), COLLATION(@j);
±------------±--------------+
| CHARSET(@j) | COLLATION(@j) |
±------------±--------------+
| utf8mb4 | utf8mb4_bin |
±------------±--------------+
JSON格式的规范化
当产生JSON格式的数据时,MySQL会把JSON规范化,当有相同的key存在时,后面的key会覆盖前面的key。8.0.3及之前版本有bug。
举例:
mysql> SELECT JSON_OBJECT(‘key1’, 1, ‘key2’, ‘abc’, ‘key1’, ‘def’);
±-----------------------------------------------------+
| JSON_OBJECT(‘key1’, 1, ‘key2’, ‘abc’, ‘key1’, ‘def’) |
±-----------------------------------------------------+
| {“key1”: “def”, “key2”: “abc”} |
±-----------------------------------------------------+
mysql> CREATE TABLE t1 (c1 JSON);
mysql> INSERT INTO t1 VALUES
> (’{“x”: 17, “x”: “red”}’),
> (’{“x”: 17, “x”: “red”, “x”: [3, 5, 7]}’);
mysql> SELECT c1 FROM t1;
±-----------------+
| c1 |
±-----------------+
| {“x”: “red”} |
| {“x”: [3, 5, 7]} |
±-----------------+
JSON的路径表达式
MySQL用路径表达式对JSON格式的数据进行查询。
路径表达式中用$代表JSON值。
用key值代表该key对应的元素。
用[N]代表JSON数组中的第N个元素。序号从0开始。
用[M to N]代表JSON数组中第M个至第N个元素。序号从0开始。
用.[]通配符代表JSON对象的所有子元素。
用[]通配符代表JSON数组的所有元素。
用**通配符代表用某字符开头或结尾的元素。
在JSON数组中查询
如果用$代表以下JSON:
[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][0] 指向 99。 注意到
[2][0]指向99。注意到[1].a中的a可以不写引号。而如果key名中有空格,则必须加引号,见后面的例子。
在JSON对象中查询
如果用$代表以下JSON:
{“a fish”: “shark”, “a bird”: “sparrow”}
那么:
$.“a fish” 指向 shark。
$.“a bird” 指向 sparrow。
注意,"a fish"这个key值中有空格,所以引号是必须加的。
*通配符的查询:
mysql> SELECT JSON_EXTRACT(’{“a”: 1, “b”: 2, “c”: [3, 4, 5]}’, ‘
.
∗
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
E
X
T
R
A
C
T
(
′
"
a
"
:
1
,
"
b
"
:
2
,
"
c
"
:
[
3
,
4
,
5
]
′
,
′
.*'); +---------------------------------------------------------+ | JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '
.∗′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONEXTRACT(′"a":1,"b":2,"c":[3,4,5]′,′.’) |
±--------------------------------------------------------+
| [1, 2, [3, 4, 5]] |
±--------------------------------------------------------+
mysql> SELECT JSON_EXTRACT(’{“a”: 1, “b”: 2, “c”: [3, 4, 5]}’, '
.
c
[
∗
]
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
E
X
T
R
A
C
T
(
′
"
a
"
:
1
,
"
b
"
:
2
,
"
c
"
:
[
3
,
4
,
5
]
′
,
′
.c[*]'); +------------------------------------------------------------+ | JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '
.c[∗]′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONEXTRACT(′"a":1,"b":2,"c":[3,4,5]′,′.c[]’) |
±-----------------------------------------------------------+
| [3, 4, 5] |
±-----------------------------------------------------------+
**通配符的查询:
mysql> SELECT JSON_EXTRACT(’{“a”: {“b”: 1}, “c”: {“b”: 2}}’, ‘
∗
∗
.
b
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
E
X
T
R
A
C
T
(
′
"
a
"
:
"
b
"
:
1
,
"
c
"
:
"
b
"
:
2
′
,
′
**.b'); +---------------------------------------------------------+ | JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '
∗∗.b′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONEXTRACT(′"a":"b":1,"c":"b":2′,′**.b’) |
±--------------------------------------------------------+
| [1, 2] |
±--------------------------------------------------------+
正序范围查询
范围查询使用[M to N]这样的表达式,注意序号是从0开始的,比如:
mysql> SELECT JSON_EXTRACT(’[1, 2, 3, 4, 5]’, ‘
[
1
t
o
3
]
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
E
X
T
R
A
C
T
(
′
[
1
,
2
,
3
,
4
,
5
]
′
,
′
[1 to 3]'); +----------------------------------------------+ | JSON_EXTRACT('[1, 2, 3, 4, 5]', '
[1to3]′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONEXTRACT(′[1,2,3,4,5]′,′[1 to 3]’) |
±---------------------------------------------+
| [2, 3, 4] |
±---------------------------------------------+
1 row in set (0.00 sec)
倒序范围查询
倒序查询使用[last-M to last-N]这样的表达式,注意逆向的序号也是从0开始的,比如:
mysql> SELECT JSON_EXTRACT(’[1, 2, 3, 4, 5]’, ‘
[
l
a
s
t
−
3
t
o
l
a
s
t
−
1
]
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
E
X
T
R
A
C
T
(
′
[
1
,
2
,
3
,
4
,
5
]
′
,
′
[last-3 to last-1]'); +--------------------------------------------------------+ | JSON_EXTRACT('[1, 2, 3, 4, 5]', '
[last−3tolast−1]′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONEXTRACT(′[1,2,3,4,5]′,′[last-3 to last-1]’) |
±-------------------------------------------------------+
| [2, 3, 4] |
±-------------------------------------------------------+
1 row in set (0.01 sec)
注意,如果last关键字用于非数组时,效果等同于先把目标对象封装成单元素数组,然后再操作,比如:
mysql> SELECT JSON_REPLACE(’“Sakila”’, ‘
[
l
a
s
t
]
′
,
10
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
R
E
P
L
A
C
E
(
′
"
S
a
k
i
l
a
"
′
,
′
[last]', 10); +-----------------------------------------+ | JSON_REPLACE('"Sakila"', '
[last]′,10);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONREPLACE(′"Sakila"′,′[last]’, 10) |
±----------------------------------------+
| 10 |
±----------------------------------------+
1 row in set (0.00 sec)
JSON的比较和排序
可支持的操作符:=, <, <=, >, >=, <>, !=, and <=>
不支持的操作符:BETWEEN,IN(),GREATEST(),LEAST()。可以把JSON中的值解析出来然后用可支持的操作符来做判断。
JSON值的比较有数据类型优先级的概念,不同优先级的数据类型比较时,由较高优先级的类型决定。数据类型的优先级从大到小分别是:
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL
下面分别介绍一下排序规则:
1,BLOB
比较两个JSON值的前N个字符,N是较短值中的字节数。如果前N个字符相同,较短值排在较大值前面。
2,BIT
同BLOB。
3,OPAQUE
同BLOB。
4,DATETIME
按时间排序。
5,TIME
按时间排序。
6,DATE
按时间排序。
7,BOOLEAN
false小于true。可能原理是false=0,true=1。
8,ARRAY
由数组中第一个不同元素决定大小,如果较短数组的全部元素都等于较长数组中的对应元素,则短数组排在前面。比如:
[] < [“a”] < [“ab”] < [“ab”, “cd”, “ef”] < [“ab”, “ef”]
9,OBJECT
如果两个JSON对象有相同的键集,而且每个键对应的值都相等,那么两个JSON对象就相等。
10,STRING
比较两个JSON值的前N个字符,N是较短值中的字符数。如果前N个字符相同,较短值排在较大值前面。
11,INTEGER, DOUBLE
比较数字大小。
12,NULL
如果比较双方有NULL,比较的结果为UNKNOWN。
JSON的函数
一,创建JSON格式数据
1,JSON_ARRAY([val[, val] …])
根据参数值创建JSON数组,每个参数都是数组中一个元素。
举例:
mysql> SELECT JSON_ARRAY(1, “abc”, NULL, TRUE, CURTIME());
±--------------------------------------------+
| JSON_ARRAY(1, “abc”, NULL, TRUE, CURTIME()) |
±--------------------------------------------+
| [1, “abc”, null, true, “10:27:46.000000”] |
±--------------------------------------------+
1 row in set (0.00 sec)
2,JSON_OBJECT([key, val[, key, val] …])
根据参数列表创建JSON对象。
按照参数的排列,第一个是key,第二个是value,第三个是key,以此类推。参数个数必须是双数。
参数列表可以为空,得到空JSON:{}。
key不能是null。否则报错。value可以是null。
数据类型错误时会报错。
举例:
mysql> SELECT JSON_OBJECT(‘id’, 87, ‘name’, ‘carrot’);
±----------------------------------------+
| JSON_OBJECT(‘id’, 87, ‘name’, ‘carrot’) |
±----------------------------------------+
| {“id”: 87, “name”: “carrot”} |
±----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OBJECT(‘id’, 87, null, ‘carrot’);
ERROR 3158 (22032): JSON documents may not contain NULL member names.
mysql> SELECT JSON_OBJECT(‘id’, 87, ‘name’, NULL);
±------------------------------------+
| JSON_OBJECT(‘id’, 87, ‘name’, NULL) |
±------------------------------------+
| {“id”: 87, “name”: null} |
±------------------------------------+
1 row in set (0.00 sec)
3,JSON_QUOTE(string)
用双引号把字符串括起来,把结果转为utf8mb4并返回。其中会经过JSON的转义。目的是得到JSON的字符串形式。
mysql> SELECT JSON_QUOTE(‘null’), JSON_QUOTE(’“null”’);
±-------------------±---------------------+
| JSON_QUOTE(‘null’) | JSON_QUOTE(’“null”’) |
±-------------------±---------------------+
| “null” | ““null”” |
±-------------------±---------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_QUOTE(’[1, 2, 3]’);
±------------------------+
| JSON_QUOTE(’[1, 2, 3]’) |
±------------------------+
| “[1, 2, 3]” |
±------------------------+
1 row in set (0.01 sec)
二,JSON的查询
1,JSON_CONTAINS(target, candidate[, path])
检查候选JSON是否包含在目标JSON中,1表示包含,0表示不包含。
target是目标元素,candidate是候选元素,path是路径表达式,如果path有值,则目标元素需要先经过路径表达式的处理再参与判断。
此函数可以使用多值索引。
另有如下规则:
1)参数可比时才能使用此函数。json_type()相同时才能使用此函数,另外integer和decimal类型可以直接比。
2)数组包含在数组中。只有目标数组的每个元素都包含在候选数组中,才算包含。
3)非数组包含在数组中。只有候选JSON对象包含在目标数组的某个元素中,才算包含。
4)非数组包含在非数组中。当且仅当候选JSON对象的每个key,在目标JSON对象中都有同名的key和同值的value,才算包含。
比如:
mysql> SELECT JSON_CONTAINS(’{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, ‘1’, ‘
.
a
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
C
O
N
T
A
I
N
S
(
′
"
a
"
:
1
,
"
b
"
:
2
,
"
c
"
:
"
d
"
:
4
′
,
′
1
′
,
′
.a'); +--------------------------------------------------------------+ | JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '1', '
.a′); +−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONCONTAINS(′"a":1,"b":2,"c":"d":4′,′1′,′.a’) |
±-------------------------------------------------------------+
| 1 |
±-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(’{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, ‘1’, ‘
.
b
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
C
O
N
T
A
I
N
S
(
′
"
a
"
:
1
,
"
b
"
:
2
,
"
c
"
:
"
d
"
:
4
′
,
′
1
′
,
′
.b'); +--------------------------------------------------------------+ | JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '1', '
.b′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONCONTAINS(′"a":1,"b":2,"c":"d":4′,′1′,′.b’) |
±-------------------------------------------------------------+
| 0 |
±-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(’{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, ‘{“d”:4}’, ‘
.
b
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
C
O
N
T
A
I
N
S
(
′
"
a
"
:
1
,
"
b
"
:
2
,
"
c
"
:
"
d
"
:
4
′
,
′
"
d
"
:
4
′
,
′
.b'); +--------------------------------------------------------------------+ | JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '{"d":4}', '
.b′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONCONTAINS(′"a":1,"b":2,"c":"d":4′,′"d":4′,′.b’) |
±-------------------------------------------------------------------+
| 0 |
±-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(’{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, ‘{“d”:4}’, ‘
.
c
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
C
O
N
T
A
I
N
S
(
′
"
a
"
:
1
,
"
b
"
:
2
,
"
c
"
:
"
d
"
:
4
′
,
′
"
d
"
:
4
′
,
′
.c'); +--------------------------------------------------------------------+ | JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '{"d":4}', '
.c′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONCONTAINS(′"a":1,"b":2,"c":"d":4′,′"d":4′,′.c’) |
±-------------------------------------------------------------------+
| 1 |
±-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(’{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, “{“d”:4}”, ‘KaTeX parse error: Can't use function '\"' in math mode at position 137: … {"d": 4}}', "{\̲"̲d\":4}", '.c’) |
±---------------------------------------------------------------------+
| 1 |
±---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(’{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, “{“d”:“4”}”, ‘KaTeX parse error: Can't use function '\"' in math mode at position 137: … {"d": 4}}', "{\̲"̲d\":\"4\"}", '.c’) |
±-------------------------------------------------------------------------+
| 0 |
±-------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(’[“abc”,“def”]’,’“abc”’);
±---------------------------------------+
| JSON_CONTAINS(’[“abc”,“def”]’,’“abc”’) |
±---------------------------------------+
| 1 |
±---------------------------------------+
注意,候选JSON和目标JSON都得是JSON格式的字符串,单个元素也得加引号,数字要写成这样:“1”,字符串要引号外面再套引号,写成这样:’“abc”’。
2,JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)
判断目标JSON中是否存在指定的路径或路径列表。
第一个参数是目标JSON。
第二个参数可以选择one或all。如果选择one,那么只要其中一条路径是存在的就返回1,否则返回0。如果选择all,那么必须所有路径都存在才返回1,否则返回0。
举例:
mysql> SELECT JSON_CONTAINS_PATH(’{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, ‘one’, ‘
.
a
′
,
′
.a', '
.a′,′.e’);
±---------------------------------------------------------------------------+
| JSON_CONTAINS_PATH(’{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, ‘one’, ‘
.
a
′
,
′
.a', '
.a′,′.e’) |
±---------------------------------------------------------------------------+
| 1 |
±---------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS_PATH(’{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, ‘all’, ‘
.
a
′
,
′
.a', '
.a′,′.e’);
±---------------------------------------------------------------------------+
| JSON_CONTAINS_PATH(’{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, ‘all’, ‘
.
a
′
,
′
.a', '
.a′,′.e’) |
±---------------------------------------------------------------------------+
| 0 |
±---------------------------------------------------------------------------+
1 row in set (0.00 sec)
3,JSON_EXTRACT(json_doc, path[, path] …)
从目标JSON中返回对应路径下的元素。如果匹配到多个元素则封装成数组。
比如:
mysql> SELECT JSON_EXTRACT(’[10, 20, [30, 40]]’, ‘
[
1
]
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
E
X
T
R
A
C
T
(
′
[
10
,
20
,
[
30
,
40
]
]
′
,
′
[1]'); +--------------------------------------------+ | JSON_EXTRACT('[10, 20, [30, 40]]', '
[1]′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONEXTRACT(′[10,20,[30,40]]′,′[1]’) |
±-------------------------------------------+
| 20 |
±-------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT(’[10, 20, [30, 40]]’, ‘
[
1
]
′
,
′
[1]', '
[1]′,′[0]’);
±---------------------------------------------------+
| JSON_EXTRACT(’[10, 20, [30, 40]]’, ‘
[
1
]
′
,
′
[1]', '
[1]′,′[0]’) |
±---------------------------------------------------+
| [20, 10] |
±---------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT(’[10, 20, [30, 40]]’, ‘
[
2
]
[
∗
]
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
E
X
T
R
A
C
T
(
′
[
10
,
20
,
[
30
,
40
]
]
′
,
′
[2][*]'); +-----------------------------------------------+ | JSON_EXTRACT('[10, 20, [30, 40]]', '
[2][∗]′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONEXTRACT(′[10,20,[30,40]]′,′[2][*]’) |
±----------------------------------------------+
| [30, 40] |
±----------------------------------------------+
1 row in set (0.00 sec)
4,->运算符
此运算符是JSON_EXTRACT()函数的简写,单个path的场景。
这个运算符几乎可以出现在sql的所有位置,而且在select,update等语句中都能用,比如:
mysql> SELECT c, JSON_EXTRACT(c, “
.
i
d
"
)
,
g
>
F
R
O
M
j
e
m
p
>
W
H
E
R
E
J
S
O
N
E
X
T
R
A
C
T
(
c
,
"
.id"), g > FROM jemp > WHERE JSON_EXTRACT(c, "
.id"),g >FROMjemp >WHEREJSONEXTRACT(c,".id”) > 1
> ORDER BY JSON_EXTRACT(c, “$.name”);
这个语句可以替换为:
mysql> SELECT c, c->"
.
i
d
"
,
g
>
F
R
O
M
j
e
m
p
>
W
H
E
R
E
c
−
>
"
.id", g > FROM jemp > WHERE c->"
.id",g >FROMjemp >WHEREc−>".id" > 1
> ORDER BY c->"$.name";
另外,在EXPLAIN结果的WARING中,->表达式被展开成json_extract()函数:
mysql> explain select a->’KaTeX parse error: Expected 'EOF', got '#' at position 1046: …003 | /* select#̲1 */ select jso….name’) AS a->'$.name'
from dev
.test_j
|
±------±-----±-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
5,->>运算符
->>运算符是加强版的->运算符,他把->运算符得到的结果去掉了引号,就像JSON_UNQUOTE ()函数一样,所以,以下三个表达式所代表的含义是一样的:
JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path
和->>运算符一样,->>运算符可以被用到sql中的很多位置。
举例:
mysql> select * from test_j;
±------------------------------+
| a |
±------------------------------+
| {“id”: “3”, “name”: “Barney”} |
| {“id”: “4”, “name”: “Betty”} |
±------------------------------+
2 rows in set (0.00 sec)
mysql> select a->’
.
n
a
m
e
′
f
r
o
m
t
e
s
t
j
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
a
−
>
′
.name' from test_j; +-------------+ | a->'
.name′fromtestj;+−−−−−−−−−−−−−+∣a−>′.name’ |
±------------+
| “Barney” |
| “Betty” |
±------------+
2 rows in set (0.00 sec)
mysql> select a->>’
.
n
a
m
e
′
f
r
o
m
t
e
s
t
j
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
a
−
>
>
′
.name' from test_j; +--------------+ | a->>'
.name′fromtestj;+−−−−−−−−−−−−−−+∣a−>>′.name’ |
±-------------+
| Barney |
| Betty |
±-------------+
2 rows in set (0.00 sec)
另外,和->运算符一样,在EXPLAIN语句结果中,->>运算符会被展开:
mysql> explain select a->>’KaTeX parse error: Expected 'EOF', got '#' at position 1091: …003 | /* select#̲1 */ select jso….name’)) AS a->>'$.name'
from dev
.test_j
|
±------±-----±--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
6,JSON_KEYS(json_doc[, path])
返回JSON中一级键值对中key的列表。如果写了path字段,则先进行路径表达式计算,然后的返回第一级键值对中key的列表。
也就是说,不会返回子元素的key。
mysql> SELECT JSON_KEYS(’{“a”: 1, “b”: {“c”: 30}}’);
±--------------------------------------+
| JSON_KEYS(’{“a”: 1, “b”: {“c”: 30}}’) |
±--------------------------------------+
| [“a”, “b”] |
±--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_KEYS(’{“a”: 1, “b”: {“c”: 30}}’, ‘
.
b
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
K
E
Y
S
(
′
"
a
"
:
1
,
"
b
"
:
"
c
"
:
30
′
,
′
.b'); +----------------------------------------------+ | JSON_KEYS('{"a": 1, "b": {"c": 30}}', '
.b′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONKEYS(′"a":1,"b":"c":30′,′.b’) |
±---------------------------------------------+
| [“c”] |
±---------------------------------------------+
1 row in set (0.00 sec)
7,JSON_OVERLAPS(json_doc1, json_doc2)
比较两个JSON是否有相同元素。也就是是否有交集。有则返回1,没有返回0。
部分匹配的情况不能算有相同元素。
两个JSON对象比较时,两者至少有一个相同name的key和相同对应value,则返回1。
两个标量比较时,则比较值是否相等。
标量和数组比较时,则判断标量是否和数组某元素相等。数据类型不同时不算相等。
比如:
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", “[2,5,7]”);
±--------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", “[2,5,7]”) |
±--------------------------------------+
| 1 |
±--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", “[2,6,8]”);
±--------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", “[2,6,8]”) |
±--------------------------------------+
| 0 |
±--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS(’[[1,2],[3,4],5]’, ‘[1,[2,3],[4,5]]’);
±----------------------------------------------------+
| JSON_OVERLAPS(’[[1,2],[3,4],5]’, ‘[1,[2,3],[4,5]]’) |
±----------------------------------------------------+
| 0 |
±----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS(’{“a”:1,“b”:10,“d”:10}’, ‘{“a”:5,“e”:10,“f”:1,“d”:20}’);
±----------------------------------------------------------------------+
| JSON_OVERLAPS(’{“a”:1,“b”:10,“d”:10}’, ‘{“a”:5,“e”:10,“f”:1,“d”:20}’) |
±----------------------------------------------------------------------+
| 0 |
±----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS(’[4,5,“6”,7]’, ‘6’);
±----------------------------------+
| JSON_OVERLAPS(’[4,5,“6”,7]’, ‘6’) |
±----------------------------------+
| 0 |
±----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS(’[4,5,6,7]’, ‘“6”’);
±----------------------------------+
| JSON_OVERLAPS(’[4,5,6,7]’, ‘“6”’) |
±----------------------------------+
| 0 |
±----------------------------------+
1 row in set (0.00 sec)
8,JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])
根据某字符串,返回在目标JSON中匹配的value的路径表达式,search_str和JSON中的value相等时算匹配。
此函数能查询子元素信息。
第一个参数json_doc是目标JSON。
第二个参数one_or_all可以选择one或者all。one表示返回一个匹配的值的路径。all表示返回所有路径。
参数search_str是要搜索的字符串。其中可以用%(百分号)代表任意多个字符,_(下划线)代表任意一个字符。
参数escape_char是转义字符。默认是\。写成空字符串或NULL时,也默认为\。
参数path是路径表达式,如果写了path,匹配结果需在路径表达式下进行。
比如:
mysql> SELECT JSON_SEARCH(’[“abc”, [{“k”: “10”}, “def”], {“x”:“abc”}, {“y”:“bcd”}]’, ‘one’, ‘abc’);
±-------------------------------------------------------------------------------------+
| JSON_SEARCH(’[“abc”, [{“k”: “10”}, “def”], {“x”:“abc”}, {“y”:“bcd”}]’, ‘one’, ‘abc’) |
±-------------------------------------------------------------------------------------+
| “
[
0
]
"
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
1
r
o
w
i
n
s
e
t
(
0.00
s
e
c
)
m
y
s
q
l
>
S
E
L
E
C
T
J
S
O
N
S
E
A
R
C
H
(
′
[
"
a
b
c
"
,
[
"
k
"
:
"
10
"
,
"
d
e
f
"
]
,
"
x
"
:
"
a
b
c
"
,
"
y
"
:
"
b
c
d
"
]
′
,
′
a
l
l
′
,
′
a
b
c
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
S
E
A
R
C
H
(
′
[
"
a
b
c
"
,
[
"
k
"
:
"
10
"
,
"
d
e
f
"
]
,
"
x
"
:
"
a
b
c
"
,
"
y
"
:
"
b
c
d
"
]
′
,
′
a
l
l
′
,
′
a
b
c
′
)
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
[
"
[0]" | +--------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc'); +--------------------------------------------------------------------------------------+ | JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc') | +--------------------------------------------------------------------------------------+ | ["
[0]" ∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+1rowinset(0.00sec) mysql>SELECTJSONSEARCH(′["abc",["k":"10","def"],"x":"abc","y":"bcd"]′,′all′,′abc′); +−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONSEARCH(′["abc",["k":"10","def"],"x":"abc","y":"bcd"]′,′all′,′abc′)∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣["[0]”, “
[
2
]
.
x
"
]
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
1
r
o
w
i
n
s
e
t
(
0.00
s
e
c
)
m
y
s
q
l
>
S
E
L
E
C
T
J
S
O
N
S
E
A
R
C
H
(
′
[
"
a
b
c
"
,
[
"
k
"
:
"
10
"
,
"
d
e
f
"
]
,
"
x
"
:
"
a
b
c
"
,
"
y
"
:
"
b
c
d
"
]
′
,
′
a
l
l
′
,
′
a
b
c
d
e
f
g
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
S
E
A
R
C
H
(
′
[
"
a
b
c
"
,
[
"
k
"
:
"
10
"
,
"
d
e
f
"
]
,
"
x
"
:
"
a
b
c
"
,
"
y
"
:
"
b
c
d
"
]
′
,
′
a
l
l
′
,
′
a
b
c
d
e
f
g
′
)
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
N
U
L
L
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
1
r
o
w
i
n
s
e
t
(
0.00
s
e
c
)
m
y
s
q
l
>
S
E
L
E
C
T
J
S
O
N
S
E
A
R
C
H
(
′
[
"
a
b
c
"
,
[
"
k
"
:
"
10
"
,
"
d
e
f
"
]
,
"
x
"
:
"
a
b
c
"
,
"
y
"
:
"
b
c
d
"
]
′
,
′
a
l
l
′
,
′
1
0
′
,
N
U
L
L
,
′
[2].x"] | +--------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abcdefg'); +------------------------------------------------------------------------------------------+ | JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abcdefg') | +------------------------------------------------------------------------------------------+ | NULL | +------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL, '
[2].x"] ∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+1rowinset(0.00sec) mysql>SELECTJSONSEARCH(′["abc",["k":"10","def"],"x":"abc","y":"bcd"]′,′all′,′abcdefg′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONSEARCH(′["abc",["k":"10","def"],"x":"abc","y":"bcd"]′,′all′,′abcdefg′)∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣NULL ∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+1rowinset(0.00sec) mysql>SELECTJSONSEARCH(′["abc",["k":"10","def"],"x":"abc","y":"bcd"]′,′all′,′10′,NULL,′[1][0].k’);
±-------------------------------------------------------------------------------------------------------+
| JSON_SEARCH(’[“abc”, [{“k”: “10”}, “def”], {“x”:“abc”}, {“y”:“bcd”}]’, ‘all’, ‘10’, NULL, '
[
1
]
[
0
]
.
k
′
)
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
"
[1][0].k') | +--------------------------------------------------------------------------------------------------------+ | "
[1][0].k′)∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣"[1][0].k” |
±-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SEARCH(’[“abc”, [{“k”: “10”}, “def”], {“x”:“abc”}, {“y”:“bcd”}]’, ‘all’, ‘%b%’, NULL, ‘
[
3
]
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
S
E
A
R
C
H
(
′
[
"
a
b
c
"
,
[
"
k
"
:
"
10
"
,
"
d
e
f
"
]
,
"
x
"
:
"
a
b
c
"
,
"
y
"
:
"
b
c
d
"
]
′
,
′
a
l
l
′
,
′
[3]'); +----------------------------------------------------------------------------------------------------+ | JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '%b%', NULL, '
[3]′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONSEARCH(′["abc",["k":"10","def"],"x":"abc","y":"bcd"]′,′all′,′[3]’) |
±---------------------------------------------------------------------------------------------------+
| “$[3].y” |
±---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
9,value MEMBER OF(json_array)
判断value是否被包含在某JSON数组中。包含则返回1,否则返回0。
数据格式不同时不算包含。
value可以用其他表达式替代。
JSON格式的字符串不能直接和数组中的JSON对象比较,会返回0,此时需要把value转成JSON类型才能返回1。
mysql> SELECT 17 MEMBER OF(’[23, “abc”, 17, “ab”, 10]’);
±------------------------------------------+
| 17 MEMBER OF(’[23, “abc”, 17, “ab”, 10]’) |
±------------------------------------------+
| 1 |
±------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ‘17’ MEMBER OF(’[23, “abc”, 17, “ab”, 10]’);
±--------------------------------------------+
| ‘17’ MEMBER OF(’[23, “abc”, 17, “ab”, 10]’) |
±--------------------------------------------+
| 0 |
±--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY(4,5) MEMBER OF(’[[3,4],[4,5]]’);
±-------------------------------------------+
| JSON_ARRAY(4,5) MEMBER OF(’[[3,4],[4,5]]’) |
±-------------------------------------------+
| 1 |
±-------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ‘{“a”:1}’ MEMBER OF(JSON_ARRAY(17, CAST(’{“a”:1}’ AS JSON), “abc”, 23));
±------------------------------------------------------------------------+
| ‘{“a”:1}’ MEMBER OF(JSON_ARRAY(17, CAST(’{“a”:1}’ AS JSON), “abc”, 23)) |
±------------------------------------------------------------------------+
| 0 |
±------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CAST(’{“a”:1}’ AS JSON) MEMBER OF(JSON_ARRAY(17, CAST(’{“a”:1}’ AS JSON), “abc”, 23));
±--------------------------------------------------------------------------------------+
| CAST(’{“a”:1}’ AS JSON) MEMBER OF(JSON_ARRAY(17, CAST(’{“a”:1}’ AS JSON), “abc”, 23)) |
±--------------------------------------------------------------------------------------+
| 1 |
±--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
三,JSON值的修改
1,JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
向JSON数组中追加元素,如果对应位置是单个元素,则和新元素一起封装成数组。
另外,在MySQL8.0中,原来的JSON_APPEND()函数不再使用。
mysql> SELECT JSON_ARRAY_APPEND(’[“a”, [“b”, “c”], “d”]’, ‘
[
1
]
′
,
1
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
A
R
R
A
Y
A
P
P
E
N
D
(
′
[
"
a
"
,
[
"
b
"
,
"
c
"
]
,
"
d
"
]
′
,
′
[1]', 1); +--------------------------------------------------------+ | JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '
[1]′,1);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONARRAYAPPEND(′["a",["b","c"],"d"]′,′[1]’, 1) |
±-------------------------------------------------------+
| [“a”, [“b”, “c”, 1], “d”] |
±-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_APPEND(’[“a”, [“b”, “c”], “d”]’, ‘
[
1
]
[
0
]
′
,
3
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
A
R
R
A
Y
A
P
P
E
N
D
(
′
[
"
a
"
,
[
"
b
"
,
"
c
"
]
,
"
d
"
]
′
,
′
[1][0]', 3); +-----------------------------------------------------------+ | JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '
[1][0]′,3); +−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONARRAYAPPEND(′["a",["b","c"],"d"]′,′[1][0]’, 3) |
±----------------------------------------------------------+
| [“a”, [[“b”, 3], “c”], “d”] |
±----------------------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT JSON_ARRAY_APPEND(’{“a”: 1}’, ‘
′
,
′
z
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
A
R
R
A
Y
A
P
P
E
N
D
(
′
"
a
"
:
1
′
,
′
', 'z'); +-----------------------------------------+ | JSON_ARRAY_APPEND('{"a": 1}', '
′,′z′); +−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONARRAYAPPEND(′"a":1′,′’, ‘z’) |
±----------------------------------------+
| [{“a”: 1}, “z”] |
±----------------------------------------+
1 row in set (0.00 sec)
2,JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)
在数组的指定位置追加元素,原位置的元素和后面的元素依次向后移一位。
指定位置超过数组上限,则添加在数组最后位置。
注意:在多个位置添加多个元素时,添加是有顺序的,后面添加的元素需要等前面的元素添加成功后重新确认具体位置。
举例:
mysql> SELECT JSON_ARRAY_INSERT(’[“a”, {“b”: [1, 2]}, [3, 4]]’, ‘
[
1
]
′
,
′
x
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
A
R
R
A
Y
I
N
S
E
R
T
(
′
[
"
a
"
,
"
b
"
:
[
1
,
2
]
,
[
3
,
4
]
]
′
,
′
[1]', 'x'); +----------------------------------------------------------------+ | JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '
[1]′,′x′); +−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONARRAYINSERT(′["a","b":[1,2],[3,4]]′,′[1]’, ‘x’) |
±---------------------------------------------------------------+
| [“a”, “x”, {“b”: [1, 2]}, [3, 4]] |
±---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_INSERT(’[“a”, {“b”: [1, 2]}, [3, 4]]’, ‘
[
100
]
′
,
′
x
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
A
R
R
A
Y
I
N
S
E
R
T
(
′
[
"
a
"
,
"
b
"
:
[
1
,
2
]
,
[
3
,
4
]
]
′
,
′
[100]', 'x'); +------------------------------------------------------------------+ | JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '
[100]′,′x′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONARRAYINSERT(′["a","b":[1,2],[3,4]]′,′[100]’, ‘x’) |
±-----------------------------------------------------------------+
| [“a”, {“b”: [1, 2]}, [3, 4], “x”] |
±-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_INSERT(’[“a”, {“b”: [1, 2]}, [3, 4]]’, ‘
[
1
]
.
b
[
0
]
′
,
′
x
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
A
R
R
A
Y
I
N
S
E
R
T
(
′
[
"
a
"
,
"
b
"
:
[
1
,
2
]
,
[
3
,
4
]
]
′
,
′
[1].b[0]', 'x'); +---------------------------------------------------------------------+ | JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '
[1].b[0]′,′x′); +−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONARRAYINSERT(′["a","b":[1,2],[3,4]]′,′[1].b[0]’, ‘x’) |
±--------------------------------------------------------------------+
| [“a”, {“b”: [“x”, 1, 2]}, [3, 4]] |
±--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_INSERT(’[“a”, {“b”: [1, 2]}, [3, 4]]’, ‘
[
0
]
′
,
′
x
′
,
′
[0]', 'x', '
[0]′,′x′,′[2][1]’, ‘y’);
±-------------------------------------------------------------------------------+
| JSON_ARRAY_INSERT(’[“a”, {“b”: [1, 2]}, [3, 4]]’, ‘
[
0
]
′
,
′
x
′
,
′
[0]', 'x', '
[0]′,′x′,′[2][1]’, ‘y’) |
±-------------------------------------------------------------------------------+
| [“x”, “a”, {“b”: [1, 2]}, [3, 4]] |
±-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
注意最后一个语句,在
[
0
]
处
添
加
x
,
在
[0]处添加x,在
[0]处添加x,在[2][1]处添加y,但是最后y没有添加成功,本来$[2]代表的应该是[3, 4],但是前面添加了x后,JSON变成了:
[“x”, “a”, {“b”: [1, 2]}, [3, 4]]
于是此时的$[2]变成了{“b”: [1, 2]},是个JSON对象,不是数组,所以无法使用此函数。
以下sql可以验证这个场景:
mysql> SELECT JSON_ARRAY_INSERT(’[“a”, {“b”: [1, 2]}, [3, 4]]’, ‘
[
0
]
′
,
′
x
′
,
′
[0]', 'x', '
[0]′,′x′,′[2].b[1]’, ‘y’);
±---------------------------------------------------------------------------------+
| JSON_ARRAY_INSERT(’[“a”, {“b”: [1, 2]}, [3, 4]]’, ‘
[
0
]
′
,
′
x
′
,
′
[0]', 'x', '
[0]′,′x′,′[2].b[1]’, ‘y’) |
±---------------------------------------------------------------------------------+
| [“x”, “a”, {“b”: [1, “y”, 2]}, [3, 4]] |
±---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可见添加y的时候$[2]已经是{“b”: [1, 2]}了。
3,JSON_INSERT(json_doc, path, val[, path, val] …)
向JSON添加键值对。
如果添加的key已经存在,则忽略此键值对,不再添加。
mysql> SELECT JSON_INSERT(’{ “a”: 1, “b”: [2, 3]}’, ‘
.
a
′
,
10
,
′
.a', 10, '
.a′,10,′.c’, ‘[true, false]’);
±-------------------------------------------------------------------------+
| JSON_INSERT(’{ “a”: 1, “b”: [2, 3]}’, ‘
.
a
′
,
10
,
′
.a', 10, '
.a′,10,′.c’, ‘[true, false]’) |
±-------------------------------------------------------------------------+
| {“a”: 1, “b”: [2, 3], “c”: “[true, false]”} |
±-------------------------------------------------------------------------+
1 row in set (0.00 sec)
从此结果可以看到,此函数不支持数据类型转换,’[true, false]'参数被当做字符串添加了,如果需要作为JSON数组添加,需要手动转换:
mysql> SELECT JSON_INSERT(’{ “a”: 1, “b”: [2, 3]}’, ‘
.
a
′
,
10
,
′
.a', 10, '
.a′,10,′.c’, CAST(’[true, false]’ AS JSON));
±---------------------------------------------------------------------------------------+
| JSON_INSERT(’{ “a”: 1, “b”: [2, 3]}’, ‘
.
a
′
,
10
,
′
.a', 10, '
.a′,10,′.c’, CAST(’[true, false]’ AS JSON)) |
±---------------------------------------------------------------------------------------+
| {“a”: 1, “b”: [2, 3], “c”: [true, false]} |
±---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4,JSON_MERGE(json_doc, json_doc[, json_doc] …)
合并多个JSON,MySQL8.0.3版本已废弃,建议使用JSON_MERGE_PRESERVE()函数。
举例:
mysql> SELECT JSON_MERGE(’[1, 2]’, ‘[true, false]’);
±--------------------------------------+
| JSON_MERGE(’[1, 2]’, ‘[true, false]’) |
±--------------------------------------+
| [1, 2, true, false] |
±--------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
±--------±-----±----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
±--------±-----±----------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | ‘JSON_MERGE’ is deprecated and will be removed in a future release. Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead |
±--------±-----±----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
5,JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] …)
合并多个JSON对象。会去重的合并。
合并规则:
1,如果两JSON合并,第一个JSON不是对象(比如标量或数组),则合并的结果是第二个JSON,即使第二个JSON也不是对象。
2,如果两JSON合并,第二个JSON不是对象(比如标量或数组),则合并的结果还是第二个JSON。为什么?这什么道理?
3,如果两个JSON中有相同的key,且value中有非对象,则value保留后面JSON的value。
4,如果两个JSON中有相同的key,且对应的value中都是JSON对象,则两个value递归合并。
5,合并完成后,value是null的键值对会被删掉。可以用这个特性来删除空值键值对,比如用一个标量和想删null值的JSON合并,记得标量放第一位,JSON放第二位。
mysql> SELECT JSON_MERGE_PATCH(’[1, 2]’, ‘[true, false]’);
±--------------------------------------------+
| JSON_MERGE_PATCH(’[1, 2]’, ‘[true, false]’) |
±--------------------------------------------+
| [true, false] |
±--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PATCH(’{“a”:1, “b”:2, “c”:null}’, ‘[]’);
±---------------------------------------------------+
| JSON_MERGE_PATCH(’{“a”:1, “b”:2, “c”:null}’, ‘[]’) |
±---------------------------------------------------+
| [] |
±---------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PATCH(’{ “a”: 1, “b”:2 }’,’{ “a”: 3, “c”:4 }’,’{ “a”: 5, “d”:6 }’);
±------------------------------------------------------------------------------+
| JSON_MERGE_PATCH(’{ “a”: 1, “b”:2 }’,’{ “a”: 3, “c”:4 }’,’{ “a”: 5, “d”:6 }’) |
±------------------------------------------------------------------------------+
| {“a”: 5, “b”: 2, “c”: 4, “d”: 6} |
±------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PATCH(’{“a”:{“x”:1}}’, ‘{“a”:{“y”:2}}’);
±---------------------------------------------------+
| JSON_MERGE_PATCH(’{“a”:{“x”:1}}’, ‘{“a”:{“y”:2}}’) |
±---------------------------------------------------+
| {“a”: {“x”: 1, “y”: 2}} |
±---------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PATCH(‘1’,’{“a”:1, “b”:2, “c”:null}’);
±-------------------------------------------------+
| JSON_MERGE_PATCH(‘1’,’{“a”:1, “b”:2, “c”:null}’) |
±-------------------------------------------------+
| {“a”: 1, “b”: 2} |
±-------------------------------------------------+
1 row in set (0.00 sec)
6,JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] …)
合并多个JSON。不去重,保留所有值的合并。
合并规则:
1,两数组或两标量合并,合成一个新数组。
2,两对象合并,递归合并成一个新对象。如有同key键值对,则几个value合并成最终的value。
3,对象和数组合并,对象转成单个元素的数组然后合并成一个新数组。
举例:
mysql> SELECT JSON_MERGE_PRESERVE(’[1, 2]’, ‘[true, false]’);
±-----------------------------------------------+
| JSON_MERGE_PRESERVE(’[1, 2]’, ‘[true, false]’) |
±-----------------------------------------------+
| [1, 2, true, false] |
±-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PRESERVE(’{“name”: “x”}’, ‘{“id”: 47}’);
±---------------------------------------------------+
| JSON_MERGE_PRESERVE(’{“name”: “x”}’, ‘{“id”: 47}’) |
±---------------------------------------------------+
| {“id”: 47, “name”: “x”} |
±---------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PRESERVE(’[1, 2]’, ‘{“id”: 47}’);
±--------------------------------------------+
| JSON_MERGE_PRESERVE(’[1, 2]’, ‘{“id”: 47}’) |
±--------------------------------------------+
| [1, 2, {“id”: 47}] |
±--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PRESERVE(’{ “a”: 1, “b”: 2 }’,’{ “a”: 3, “c”: 4 }’,’{ “a”: 5, “d”: 6 }’);
±------------------------------------------------------------------------------------+
| JSON_MERGE_PRESERVE(’{ “a”: 1, “b”: 2 }’,’{ “a”: 3, “c”: 4 }’,’{ “a”: 5, “d”: 6 }’) |
±------------------------------------------------------------------------------------+
| {“a”: [1, 3, 5], “b”: 2, “c”: 4, “d”: 6} |
±------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
感觉这个JSON_MERGE_PRESERVE()函数才是真正的合并,不漏元素。相比之下JSON_MERGE_PATCH()函数的作用更像是去重。
7,JSON_REMOVE(json_doc, path[, path] …)
从JSON中删除对应路径下的元素。
指定路径不存在也不会报错。
mysql> SELECT JSON_REMOVE(’[“a”, [“b”, “c”], “d”]’, ‘
[
1
]
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
R
E
M
O
V
E
(
′
[
"
a
"
,
[
"
b
"
,
"
c
"
]
,
"
d
"
]
′
,
′
[1]'); +-----------------------------------------------+ | JSON_REMOVE('["a", ["b", "c"], "d"]', '
[1]′); +−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONREMOVE(′["a",["b","c"],"d"]′,′[1]’) |
±----------------------------------------------+
| [“a”, “d”] |
±----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_REMOVE(’[“a”, [“b”, “c”], “d”]’, ‘
[
9
]
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
R
E
M
O
V
E
(
′
[
"
a
"
,
[
"
b
"
,
"
c
"
]
,
"
d
"
]
′
,
′
[9]'); +-----------------------------------------------+ | JSON_REMOVE('["a", ["b", "c"], "d"]', '
[9]′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONREMOVE(′["a",["b","c"],"d"]′,′[9]’) |
±----------------------------------------------+
| [“a”, [“b”, “c”], “d”] |
±----------------------------------------------+
1 row in set (0.00 sec)
8,JSON_REPLACE(json_doc, path, val[, path, val] …)
替换JSON中的值。
如果路径不存在,则忽略。
mysql> SELECT JSON_REPLACE(’{ “a”: 1, “b”: [2, 3]}’, ‘
.
a
′
,
10
,
′
.a', 10, '
.a′,10,′.c’, ‘[true, false]’);
±--------------------------------------------------------------------------+
| JSON_REPLACE(’{ “a”: 1, “b”: [2, 3]}’, ‘
.
a
′
,
10
,
′
.a', 10, '
.a′,10,′.c’, ‘[true, false]’) |
±--------------------------------------------------------------------------+
| {“a”: 10, “b”: [2, 3]} |
±--------------------------------------------------------------------------+
1 row in set (0.00 sec)
9,JSON_SET(json_doc, path, val[, path, val] …)
替换JSON中的值。
如果路径不存在,则添加该值。
如果修改的是数组,路径超过了数组上限,则把元素添加到数组末尾。
mysql> SELECT JSON_SET(’{ “a”: 1, “b”: [2, 3]}’, ‘
.
a
′
,
10
,
′
.a', 10, '
.a′,10,′.c’, ‘[true, false]’);
±----------------------------------------------------------------------+
| JSON_SET(’{ “a”: 1, “b”: [2, 3]}’, ‘
.
a
′
,
10
,
′
.a', 10, '
.a′,10,′.c’, ‘[true, false]’) |
±----------------------------------------------------------------------+
| {“a”: 10, “b”: [2, 3], “c”: “[true, false]”} |
±----------------------------------------------------------------------+
1 row in set (0.00 sec)
所以,JSON_SET(),JSON_INSERT(),和JSON_REPLACE()三者的区别在于:
JSON_SET():路径存在则替换值,路径不存在则新增。
JSON_INSERT():只负责新增。
JSON_REPLACE():只负责替换已存在的值。
10,JSON_UNQUOTE(json_val)
去掉JSON值的双引号,并返回utf8mb4格式的字符串。
此函数可以识别转义字符。
mysql> SELECT ‘“abc”’, JSON_UNQUOTE(’“abc”’);
±------±----------------------+
| “abc” | JSON_UNQUOTE(’“abc”’) |
±------±----------------------+
| “abc” | abc |
±------±----------------------+
1 row in set (0.00 sec)
mysql> SELECT ‘[1, 2, 3]’, JSON_UNQUOTE(’[1, 2, 3]’);
±----------±--------------------------+
| [1, 2, 3] | JSON_UNQUOTE(’[1, 2, 3]’) |
±----------±--------------------------+
| [1, 2, 3] | [1, 2, 3] |
±----------±--------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_UNQUOTE(’"\t\u0032"’);
±-----------------------------+
| JSON_UNQUOTE(’"\t\u0032"’) |
±-----------------------------+
| 2 |
±-----------------------------+
四,JSON值的属性
1,JSON_DEPTH(json_doc)
返回JSON值的最大深度。
空数组,空对象,标量的深度为1。
仅包含深度为1的元素的数组或对象的深度为2。
其他情况以此类推。
举例:
mysql> SELECT JSON_DEPTH(’{}’), JSON_DEPTH(’[]’), JSON_DEPTH(‘true’);
±-----------------±-----------------±-------------------+
| JSON_DEPTH(’{}’) | JSON_DEPTH(’[]’) | JSON_DEPTH(‘true’) |
±-----------------±-----------------±-------------------+
| 1 | 1 | 1 |
±-----------------±-----------------±-------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_DEPTH(’[10, 20]’), JSON_DEPTH(’[[], {}]’);
±-----------------------±-----------------------+
| JSON_DEPTH(’[10, 20]’) | JSON_DEPTH(’[[], {}]’) |
±-----------------------±-----------------------+
| 2 | 2 |
±-----------------------±-----------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_DEPTH(’[10, {“a”: 20}]’);
±------------------------------+
| JSON_DEPTH(’[10, {“a”: 20}]’) |
±------------------------------+
| 3 |
±------------------------------+
1 row in set (0.00 sec)
2,JSON_LENGTH(json_doc[, path])
返回JSON值的长度。如果写了path,返回对应路径下的JSON长度。
标量的长度是1。
数组的长度是元素的数量。
对象的长度是成员数量。
嵌套的数组不会被计算长度。
举例:
mysql> SELECT JSON_LENGTH(’[1, 2, {“a”: 3}]’);
±--------------------------------+
| JSON_LENGTH(’[1, 2, {“a”: 3}]’) |
±--------------------------------+
| 3 |
±--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_LENGTH(’{“a”: 1, “b”: {“c”: 30}}’);
±----------------------------------------+
| JSON_LENGTH(’{“a”: 1, “b”: {“c”: 30}}’) |
±----------------------------------------+
| 2 |
±----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_LENGTH(’{“a”: 1, “b”: {“c”: 30}}’, ‘
.
b
′
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
L
E
N
G
T
H
(
′
"
a
"
:
1
,
"
b
"
:
"
c
"
:
30
′
,
′
.b'); +------------------------------------------------+ | JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '
.b′);+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONLENGTH(′"a":1,"b":"c":30′,′.b’) |
±-----------------------------------------------+
| 1 |
±-----------------------------------------------+
1 row in set (0.00 sec)
3,JSON_TYPE(json_val)
返回JSON类型。返回值是一个utf8mb4字符串。
可返回的JSON类型有:
OBJECT。JSON对象。
ARRAY。JSON数组。
BOOLEAN。JSON的true或false。
NULL。JSON的null值。这个返回值是大写的。
INTEGER。TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT类型标量。
DOUBLE。DOUBLE和FLOAT类型标量。
DECIMAL。DECIMAL和NUMERIC类型标量。
DATETIME。DATETIME和TIMESTAMP类型标量。
DATE。DATE类型标量。
TIME。TIME类型标量。
STRING。MySQL中utf8格式标量,比如:CHAR,VARCHAR,TEXT,ENUM,SET。
BLOB。MySQL中二进制格式标量,比如:BINARY,VARBINARY,BLOB,BIT。
OPAQUE。raw bits格式。
举例:
mysql> SELECT JSON_TYPE(’{“a”: [10, true]}’);
±-------------------------------+
| JSON_TYPE(’{“a”: [10, true]}’) |
±-------------------------------+
| OBJECT |
±-------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_TYPE(JSON_EXTRACT(’{“a”: [10, true]}’, ‘
.
a
[
1
]
′
)
)
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
T
Y
P
E
(
J
S
O
N
E
X
T
R
A
C
T
(
′
"
a
"
:
[
10
,
t
r
u
e
]
′
,
′
.a[1]')); +--------------------------------------------------------+ | JSON_TYPE(JSON_EXTRACT('{"a": [10, true]}', '
.a[1]′)); +−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣JSONTYPE(JSONEXTRACT(′"a":[10,true]′,′.a[1]’)) |
±-------------------------------------------------------+
| BOOLEAN |
±-------------------------------------------------------+
1 row in set (0.00 sec)
4,JSON_VALID(val)
判断JSON值是否符合JSON规范。符合返回1,不符合返回0。
mysql> SELECT JSON_VALID(’{“a”: 1}’);
±-----------------------+
| JSON_VALID(’{“a”: 1}’) |
±-----------------------+
| 1 |
±-----------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_VALID(‘hello’), JSON_VALID(’“hello”’);
±--------------------±----------------------+
| JSON_VALID(‘hello’) | JSON_VALID(’“hello”’) |
±--------------------±----------------------+
| 0 | 1 |
±--------------------±----------------------+
1 row in set (0.00 sec)
五,JSON临时表函数
JSON临时表函数可以把一个JSON值变成一个临时表,并返回表格的一些信息。
建立临时表的函数如下:
JSON_TABLE(
expr,
path COLUMNS (column_list)
) [AS] alias
解析一下其中的各个表达式:
1,expr是JSON值。可以是个常量,比如’{“a”:1}’。可以是一列值,比如t1.json_data,t1是之前定义的JSON临时表。可以是返回JSON值的函数,比如:JSON_EXTRACT(t1,jsn_data,’$.post.comments’)。
2,path是路径表达式,JSON值要先经过路径表达式的筛选。
3,alias是临时表的表名。AS可以不写。
4,COLUMNS是临时表的列。当列中用到path路径时,$从父级路径表达式继承,属于相对路径。
COLUMNS的单个元素都可以有以下选择:
1),name FOR ORDINALITY
此列代表行号。name是自定义列名。此列内容是一个自增的计数器,类型是unsigned int,初始为1。类似MySQL的自增id。如果列中包含NESTED PATH语句,会把一行拆成多行,此时这些行的行号都是一样的,也就是第一级行的行号。
2),name type PATH string_path [on_empty] [on_error]
此列表示按照路径表达式查询JSON中的值。name是列名。type是要求返回的数据格式。string_path是路径表达式。
[on_empty]用于路径表达式值不存在的时候的默认值。
[on_empty]可选的写法有:
NULL ON EMPTY。显示为NULL,此为默认方式。
ERROR ON EMPTY。显示为ERROR。
DEFAULT json_string ON EMPTY。显示一个默认的JSON值。
[on_error]用于路径表达式下报错的时候的默认值。报错的场景比如保存时的精度错误,格式错误等。
[on_error]可选的写法有:
NULL ON ERROR。显示为NULL,此为默认方式。
ERROR ON ERROR。显示为ERROR。
DEFAULT json_string ON ERROR。显示一个默认的JSON值。
下面是一个使用ON EMPTY和ON ERROR的例子:
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> ‘[{“a”:“3”},{“a”:2},{“b”:1},{“a”:0},{“a”:[1,2]}]’,
-> “
[
∗
]
"
−
>
C
O
L
U
M
N
S
(
−
>
r
o
w
i
d
F
O
R
O
R
D
I
N
A
L
I
T
Y
,
−
>
a
c
V
A
R
C
H
A
R
(
100
)
P
A
T
H
"
[*]" -> COLUMNS( -> rowid FOR ORDINALITY, -> ac VARCHAR(100) PATH "
[∗]" −> COLUMNS( −> rowidFORORDINALITY, −> acVARCHAR(100)PATH".a” DEFAULT ‘111’ ON EMPTY DEFAULT ‘999’ ON ERROR
-> )
-> ) AS tt;
±------±-----+
| rowid | ac |
±------±-----+
| 1 | 3 |
| 2 | 2 |
| 3 | 111 |
| 4 | 0 |
| 5 | 999 |
±------±-----+
5 rows in set (0.01 sec)
可见,第3行从{“b”:1}获取"$.a"时不存在,显示的是ON EMPTY的默认值111。
第5行从{“a”:[1,2]}获取"$.a"时得到了数组,而不是列中定义的VARCHAR(100),类型错误,显示的是ON ERROR默认的999。
3),name type EXISTS PATH path
指定路径下的值是否存在。存在返回1,不存在返回0。
4),NESTED [PATH] path COLUMNS (column_list)
将JSON中嵌套结构的JSON对象或数组元素拆成单独的行。
[PATH]关键字可以不写。
column_list可以写多个,可写的内容和临时表函数可用的column_list一样,name FOR ORDINALITY之类的都可以写。
对于同一元素中的PATH和NESTED PATH,得到的结果类似一个内连接。
如果存在多个NESTED PATH,则会对每个NESTED PATH单独生成一组记录,不会用连接的形式展示。
举例:
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> ‘[ {“a”: 1, “b”: [11,111]}, {“a”: 2, “b”: [22,222]}, {“a”:3}]’,
-> ‘
[
∗
]
′
C
O
L
U
M
N
S
(
−
>
i
d
F
O
R
O
R
D
I
N
A
L
I
T
Y
,
−
>
a
I
N
T
P
A
T
H
′
[*]' COLUMNS( -> id FOR ORDINALITY, -> a INT PATH '
[∗]′COLUMNS( −> idFORORDINALITY, −> aINTPATH′.a’,
-> NESTED PATH ‘
.
b
[
∗
]
′
C
O
L
U
M
N
S
(
b
I
N
T
P
A
T
H
′
.b[*]' COLUMNS (b INT PATH '
.b[∗]′COLUMNS(bINTPATH′’)
-> )
-> ) AS jt
-> WHERE b IS NOT NULL;
±-----±-----±-----+
| id | a | b |
±-----±-----±-----+
| 1 | 1 | 11 |
| 1 | 1 | 111 |
| 2 | 2 | 22 |
| 2 | 2 | 222 |
±-----±-----±-----+
4 rows in set (0.00 sec)
可以看到,前两行id都是1,他们都是从数组的第一个元素{“a”: 1, “b”: [11,111]}得来的,这两行的a列和b列就类似’
.
a
′
和
′
.a'和'
.a′和′.b[*]'的一个内连接。
再比如:
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> ‘[{“a”: 1, “b”: [11,111]}, {“a”: 2, “b”: [22,222,2222]}]’,
-> ‘
[
∗
]
′
C
O
L
U
M
N
S
(
−
>
a
I
N
T
P
A
T
H
′
[*]' COLUMNS( -> a INT PATH '
[∗]′COLUMNS( −> aINTPATH′.a’,
-> NESTED PATH ‘
.
b
[
∗
]
′
C
O
L
U
M
N
S
(
b
1
I
N
T
P
A
T
H
′
.b[*]' COLUMNS (b1 INT PATH '
.b[∗]′COLUMNS(b1INTPATH′’),
-> NESTED PATH ‘
.
b
[
∗
]
′
C
O
L
U
M
N
S
(
b
2
I
N
T
P
A
T
H
′
.b[*]' COLUMNS (b2 INT PATH '
.b[∗]′COLUMNS(b2INTPATH′’)
-> )
-> ) AS jt;
±-----±-----±-----+
| a | b1 | b2 |
±-----±-----±-----+
| 1 | 11 | NULL |
| 1 | 111 | NULL |
| 1 | NULL | 11 |
| 1 | NULL | 111 |
| 2 | 22 | NULL |
| 2 | 222 | NULL |
| 2 | 2222 | NULL |
| 2 | NULL | 22 |
| 2 | NULL | 222 |
| 2 | NULL | 2222 |
±-----±-----±-----+
10 rows in set (0.00 sec)
可以看到,前4条数据是由{“a”: 1, “b”: [11,111]}得来的,b1和b2列理论上能获得11和111两个值,但是查询结果给b1和b2分别生成了2条记录。后6条数据是由{“a”: 2, “b”: [22,222,2222]}得来的,b1和b2列能获得22,222,2222三个值,但是查询结果给b1和b2分别生成了3条记录。
稍微复杂一点的情况:
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> '[{“a”: “a_val”,
'> “b”: [{“c”: “c_val”, “l”: [1,2]}]},
'> {“a”: “a_val”,
‘> “b”: [{“c”: “c_val”,“l”: [11]}, {“c”: “c_val”, “l”: [22]}]}]’,
-> ‘
[
∗
]
′
C
O
L
U
M
N
S
(
−
>
t
o
p
o
r
d
F
O
R
O
R
D
I
N
A
L
I
T
Y
,
−
>
a
p
a
t
h
V
A
R
C
H
A
R
(
10
)
P
A
T
H
′
[*]' COLUMNS( -> top_ord FOR ORDINALITY, -> apath VARCHAR(10) PATH '
[∗]′COLUMNS( −> topordFORORDINALITY, −> apathVARCHAR(10)PATH′.a’,
-> NESTED PATH ‘
.
b
[
∗
]
′
C
O
L
U
M
N
S
(
−
>
b
p
a
t
h
V
A
R
C
H
A
R
(
10
)
P
A
T
H
′
.b[*]' COLUMNS ( -> bpath VARCHAR(10) PATH '
.b[∗]′COLUMNS( −> bpathVARCHAR(10)PATH′.c’,
-> ord FOR ORDINALITY,
-> NESTED PATH ‘
.
l
[
∗
]
′
C
O
L
U
M
N
S
(
l
p
a
t
h
v
a
r
c
h
a
r
(
10
)
P
A
T
H
′
.l[*]' COLUMNS (lpath varchar(10) PATH '
.l[∗]′COLUMNS(lpathvarchar(10)PATH′’)
-> )
-> )
-> ) as jt;
±--------±------±------±-----±------+
| top_ord | apath | bpath | ord | lpath |
±--------±------±------±-----±------+
| 1 | a_val | c_val | 1 | 1 |
| 1 | a_val | c_val | 1 | 2 |
| 2 | a_val | c_val | 1 | 11 |
| 2 | a_val | c_val | 2 | 22 |
±--------±------±------±-----±------+
4 rows in set (0.00 sec)
六,JSON格式校验
1,JSON_SCHEMA_VALID(schema,document)
校验document文档是否符合schema定义的JSON规则。
document和schema必须都是标准的JSON格式。
schema中的require参数表示必须要有的key。
假如定义一个schema:
mysql> SET @schema = '{
'> “id”: “http://json-schema.org/geo”,
'> “$schema”: “http://json-schema.org/draft-04/schema#”,
'> “description”: “A geographical coordinate”,
'> “type”: “object”,
'> “properties”: {
'> “latitude”: {
'> “type”: “number”,
'> “minimum”: -90,
'> “maximum”: 90
'> },
'> “longitude”: {
'> “type”: “number”,
'> “minimum”: -180,
'> “maximum”: 180
'> }
'> },
'> “required”: [“latitude”, “longitude”]
‘> }’;
Query OK, 0 rows affected (0.00 sec)
然后定义document:
mysql> SET @document = '{
'> “latitude”: 63.444697,
'> “longitude”: 10.445118
‘> }’;
Query OK, 0 rows affected (0.00 sec)
最后用JSON_SCHEMA_VALID(schema,document)函数来校验:
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
±--------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
±--------------------------------------+
| 1 |
±--------------------------------------+
1 row in set (0.00 sec)
返回1即为符合schema定义的规范。
下面把document改成空的:
mysql> SET @document = ‘{}’;
Query OK, 0 rows affected (0.00 sec)
再校验一下:
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
±--------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
±--------------------------------------+
| 0 |
±--------------------------------------+
1 row in set (0.00 sec)
返回0表示校验失败。
2,JSON_SCHEMA_VALIDATION_REPORT(schema,document)
校验document文档是否符合schema定义的JSON规则,并返回JSON格式的校验报告。
如果校验成功,则返回:{“valid”: true}。
如果校验失败,则返回的校验报告中会包含以下内容:
valid。校验失败则为false。
reason。失败原因。
schema-location。校验失败的节点在schema中的位置。
document-location。校验失败的节点在document中的位置。
schema-failed-keyword。包含校验失败节点的一段schema的关键字。
一个校验失败的场景:
mysql> SET @schema = '{
'> “id”: “http://json-schema.org/geo”,
'> “$schema”: “http://json-schema.org/draft-04/schema#”,
'> “description”: “A geographical coordinate”,
'> “type”: “object”,
'> “properties”: {
'> “latitude”: {
'> “type”: “number”,
'> “minimum”: -90,
'> “maximum”: 90
'> },
'> “longitude”: {
'> “type”: “number”,
'> “minimum”: -180,
'> “maximum”: 180
'> }
'> },
'> “required”: [“latitude”, “longitude”]
‘> }’;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @document = '{
'> “latitude”: 63.444697,
'> “longitude”: 310.445118
‘> }’;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document));
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)) |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
“valid”: false,
“reason”: “The JSON document location ‘#/longitude’ failed requirement ‘maximum’ at JSON Schema location ‘#/properties/longitude’”,
“schema-location”: “#/properties/longitude”,
“document-location”: “#/longitude”,
“schema-failed-keyword”: “maximum”
} |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_PRETTY()函数的作用是让返回的结果格式更好看一些,否则是一行显示。
七,JSON辅助函数
1,JSON_PRETTY(json_val)
格式化JSON值,让输出更好看一些。
参数必须是JSON值或者是符合JSON格式的字符串。
举例:
mysql> SELECT JSON_PRETTY(’[“a”,1,{“key1”:
‘> “value1”},“5”, “77” ,
‘> {“key2”:[“value3”,“valueX”,
‘> “valueY”]},“j”, “2” ]’);
±------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(’[“a”,1,{“key1”:
“value1”},“5”, “77” ,
{“key2”:[“value3”,“valueX”,
“valueY”]},“j”, “2” ]’) |
±------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
“a”,
1,
{
“key1”: “value1”
},
“5”,
“77”,
{
“key2”: [
“value3”,
“valueX”,
“valueY”
]
},
“j”,
“2”
] |
±------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2,JSON_STORAGE_FREE(json_val)
此函数的功能是,在使用JSON_SET(),JSON_REPLACE(),JSON_REMOVE()函数修改了列值之后,JSON释放了多少空间。
多次使用这三个函数修改列值,此函数返回值会累加。
不使用这三个函数修改列值时,比如直接用set关键字修改列值,则此函数返回0。
只对修改表中存储JSON值的场景有效,如果用三个函数修改用户变量,此函数返回值依然是0。
参数是JSON字符串,返回值会是0。
比如:
mysql> select * from test_j;
±---------------------------------------------+
| a |
±---------------------------------------------+
| {“a”: 10, “b”: “wxyz”, “c”: “[true, false]”} |
±---------------------------------------------+
1 row in set (0.00 sec)
mysql> update test_j SET a = JSON_SET(a, “
.
a
"
,
10
,
"
.a", 10, "
.a",10,".b”, “wxyz”, “
.
c
"
,
1
)
;
Q
u
e
r
y
O
K
,
1
r
o
w
a
f
f
e
c
t
e
d
(
0.00
s
e
c
)
R
o
w
s
m
a
t
c
h
e
d
:
1
C
h
a
n
g
e
d
:
1
W
a
r
n
i
n
g
s
:
0
m
y
s
q
l
>
s
e
l
e
c
t
∗
f
r
o
m
t
e
s
t
j
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
a
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
"
a
"
:
10
,
"
b
"
:
"
w
x
y
z
"
,
"
c
"
:
1
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
1
r
o
w
i
n
s
e
t
(
0.00
s
e
c
)
m
y
s
q
l
>
S
E
L
E
C
T
J
S
O
N
S
T
O
R
A
G
E
F
R
E
E
(
a
)
F
R
O
M
t
e
s
t
j
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
J
S
O
N
S
T
O
R
A
G
E
F
R
E
E
(
a
)
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
∣
14
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
1
r
o
w
i
n
s
e
t
(
0.00
s
e
c
)
m
y
s
q
l
>
u
p
d
a
t
e
t
e
s
t
j
S
E
T
a
=
J
S
O
N
S
E
T
(
a
,
"
.c", 1); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test_j; +--------------------------------+ | a | +--------------------------------+ | {"a": 10, "b": "wxyz", "c": 1} | +--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_STORAGE_FREE(a) FROM test_j; +----------------------+ | JSON_STORAGE_FREE(a) | +----------------------+ | 14 | +----------------------+ 1 row in set (0.00 sec) mysql> update test_j SET a = JSON_SET(a, "
.c",1); QueryOK,1rowaffected(0.00sec)Rowsmatched:1 Changed:1 Warnings:0 mysql>select∗fromtestj;+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣a ∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+∣"a":10,"b":"wxyz","c":1∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+1rowinset(0.00sec) mysql>SELECTJSONSTORAGEFREE(a)FROMtestj;+−−−−−−−−−−−−−−−−−−−−−−+∣JSONSTORAGEFREE(a)∣+−−−−−−−−−−−−−−−−−−−−−−+∣ 14∣+−−−−−−−−−−−−−−−−−−−−−−+1rowinset(0.00sec) mysql> updatetestjSETa=JSONSET(a,".a”, 10, “
.
b
"
,
"
w
x
"
,
"
.b", "wx", "
.b","wx",".c”, 1);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT JSON_STORAGE_FREE(a) FROM test_j;
±---------------------+
| JSON_STORAGE_FREE(a) |
±---------------------+
| 16 |
±---------------------+
1 row in set (0.00 sec)
mysql> update test_j set a = ‘{“a”: 10, “b”: “wx”}’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT JSON_STORAGE_FREE(a) FROM test_j;
±---------------------+
| JSON_STORAGE_FREE(a) |
±---------------------+
| 0 |
±---------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_STORAGE_FREE(’{“a”: 10, “b”: “wxyz”, “c”: “1”}’);
±------------------------------------------------------+
| JSON_STORAGE_FREE(’{“a”: 10, “b”: “wxyz”, “c”: “1”}’) |
±------------------------------------------------------+
| 0 |
±------------------------------------------------------+
1 row in set (0.00 sec)
3,JSON_STORAGE_SIZE(json_val)
此函数返回的是一个JSON值转换成二进制后占用的磁盘空间。
如果参数是一个JSON格式的列,则表示此列的JSON值转换成二进制后占用的磁盘空间。
如果参数是一个JSON格式的字符串,则表示此字符串代表的JSON值转换成二进制后会占用的磁盘空间。
JSON_SET(),JSON_REPLACE(),JSON_REMOVE()三个函数会部分更新JSON值,不会导致此函数的返回值发生变化。直接用set关键字修改列值会导致此函数返回值发生变化。
因为用户变量不能部分修改,所以当修改用户变量时,此函数的返回值会立刻变化。
举例:
mysql> select * from test_j;
±----------------------------------------------+
| a |
±----------------------------------------------+
| {“a”: 1000, “b”: “wxyz”, “c”: “[1, 3, 5, 7]”} |
±----------------------------------------------+
1 row in set (0.00 sec)
mysql> select
-> a,
-> JSON_STORAGE_SIZE(a) AS Size,
-> JSON_STORAGE_FREE(a) AS Free
-> from test_j;
±----------------------------------------------±-----±-----+
| a | Size | Free |
±----------------------------------------------±-----±-----+
| {“a”: 1000, “b”: “wxyz”, “c”: “[1, 3, 5, 7]”} | 47 | 0 |
±----------------------------------------------±-----±-----+
1 row in set (0.00 sec)
mysql> UPDATE test_j SET a = JSON_SET(a, "
.
b
"
,
"
a
"
)
;
Q
u
e
r
y
O
K
,
1
r
o
w
a
f
f
e
c
t
e
d
(
0.01
s
e
c
)
R
o
w
s
m
a
t
c
h
e
d
:
1
C
h
a
n
g
e
d
:
1
W
a
r
n
i
n
g
s
:
0
m
y
s
q
l
>
s
e
l
e
c
t
−
>
a
,
−
>
J
S
O
N
S
T
O
R
A
G
E
S
I
Z
E
(
a
)
A
S
S
i
z
e
,
−
>
J
S
O
N
S
T
O
R
A
G
E
F
R
E
E
(
a
)
A
S
F
r
e
e
−
>
f
r
o
m
t
e
s
t
j
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
−
−
−
−
−
−
+
−
−
−
−
−
−
+
∣
a
∣
S
i
z
e
∣
F
r
e
e
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
−
−
−
−
−
−
+
−
−
−
−
−
−
+
∣
"
a
"
:
1000
,
"
b
"
:
"
a
"
,
"
c
"
:
"
[
1
,
3
,
5
,
7
]
"
∣
47
∣
3
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
−
−
−
−
−
−
+
−
−
−
−
−
−
+
1
r
o
w
i
n
s
e
t
(
0.00
s
e
c
)
m
y
s
q
l
>
u
p
d
a
t
e
t
e
s
t
j
s
e
t
a
=
′
"
a
"
:
4.55
,
"
b
"
:
"
w
x
y
z
"
,
"
c
"
:
"
[
t
r
u
e
,
f
a
l
s
e
]
"
′
;
Q
u
e
r
y
O
K
,
1
r
o
w
a
f
f
e
c
t
e
d
(
0.01
s
e
c
)
R
o
w
s
m
a
t
c
h
e
d
:
1
C
h
a
n
g
e
d
:
1
W
a
r
n
i
n
g
s
:
0
m
y
s
q
l
>
s
e
l
e
c
t
−
>
a
,
−
>
J
S
O
N
S
T
O
R
A
G
E
S
I
Z
E
(
a
)
A
S
S
i
z
e
,
−
>
J
S
O
N
S
T
O
R
A
G
E
F
R
E
E
(
a
)
A
S
F
r
e
e
−
>
f
r
o
m
t
e
s
t
j
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
−
−
−
−
−
−
+
−
−
−
−
−
−
+
∣
a
∣
S
i
z
e
∣
F
r
e
e
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
−
−
−
−
−
−
+
−
−
−
−
−
−
+
∣
"
a
"
:
4.55
,
"
b
"
:
"
w
x
y
z
"
,
"
c
"
:
"
[
t
r
u
e
,
f
a
l
s
e
]
"
∣
56
∣
0
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
−
−
−
−
−
−
+
−
−
−
−
−
−
+
1
r
o
w
i
n
s
e
t
(
0.00
s
e
c
)
m
y
s
q
l
>
S
E
T
@
j
=
′
[
100
,
"
s
a
k
i
l
a
"
,
[
1
,
3
,
5
]
,
425.05
]
′
;
Q
u
e
r
y
O
K
,
0
r
o
w
s
a
f
f
e
c
t
e
d
(
0.00
s
e
c
)
m
y
s
q
l
>
S
E
L
E
C
T
@
j
,
J
S
O
N
S
T
O
R
A
G
E
S
I
Z
E
(
@
j
)
A
S
S
i
z
e
;
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
−
−
−
−
−
−
+
∣
@
j
∣
S
i
z
e
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
−
−
−
−
−
−
+
∣
[
100
,
"
s
a
k
i
l
a
"
,
[
1
,
3
,
5
]
,
425.05
]
∣
45
∣
+
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
−
+
−
−
−
−
−
−
+
1
r
o
w
i
n
s
e
t
(
0.01
s
e
c
)
m
y
s
q
l
>
S
E
T
@
j
=
J
S
O
N
S
E
T
(
@
j
,
′
.b", "a"); Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select -> a, -> JSON_STORAGE_SIZE(a) AS Size, -> JSON_STORAGE_FREE(a) AS Free -> from test_j; +--------------------------------------------+------+------+ | a | Size | Free | +--------------------------------------------+------+------+ | {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} | 47 | 3 | +--------------------------------------------+------+------+ 1 row in set (0.00 sec) mysql> update test_j set a = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select -> a, -> JSON_STORAGE_SIZE(a) AS Size, -> JSON_STORAGE_FREE(a) AS Free -> from test_j; +------------------------------------------------+------+------+ | a | Size | Free | +------------------------------------------------+------+------+ | {"a": 4.55, "b": "wxyz", "c": "[true, false]"} | 56 | 0 | +------------------------------------------------+------+------+ 1 row in set (0.00 sec) mysql> SET @j = '[100, "sakila", [1, 3, 5], 425.05]'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size; +------------------------------------+------+ | @j | Size | +------------------------------------+------+ | [100, "sakila", [1, 3, 5], 425.05] | 45 | +------------------------------------+------+ 1 row in set (0.01 sec) mysql> SET @j = JSON_SET(@j, '
.b","a"); QueryOK,1rowaffected(0.01sec)Rowsmatched:1 Changed:1 Warnings:0 mysql>select −> a, −> JSONSTORAGESIZE(a)ASSize, −> JSONSTORAGEFREE(a)ASFree −>fromtestj;+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−−+−−−−−−+∣a ∣Size∣Free∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−−+−−−−−−+∣"a":1000,"b":"a","c":"[1,3,5,7]"∣ 47∣ 3∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−−+−−−−−−+1rowinset(0.00sec) mysql>updatetestjseta=′"a":4.55,"b":"wxyz","c":"[true,false]"′;QueryOK,1rowaffected(0.01sec)Rowsmatched:1 Changed:1 Warnings:0 mysql>select −> a, −> JSONSTORAGESIZE(a)ASSize, −> JSONSTORAGEFREE(a)ASFree −>fromtestj;+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−−+−−−−−−+∣a ∣Size∣Free∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−−+−−−−−−+∣"a":4.55,"b":"wxyz","c":"[true,false]"∣ 56∣ 0∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−−+−−−−−−+1rowinset(0.00sec) mysql>SET@j=′[100,"sakila",[1,3,5],425.05]′;QueryOK,0rowsaffected(0.00sec) mysql>SELECT@j,JSONSTORAGESIZE(@j)ASSize;+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−−+∣@j ∣Size∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−−+∣[100,"sakila",[1,3,5],425.05]∣ 45∣+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−−+1rowinset(0.01sec) mysql>SET@j=JSONSET(@j,′[1]’, “json”);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
±---------------------------------±-----+
| @j | Size |
±---------------------------------±-----+
| [100, “json”, [1, 3, 5], 425.05] | 43 |
±---------------------------------±-----+
1 row in set (0.00 sec)
mysql> SELECT
-> JSON_STORAGE_SIZE(’[100, “sakila”, [1, 3, 5], 425.05]’) AS A,
-> JSON_STORAGE_SIZE(’{“a”: 1000, “b”: “a”, “c”: “[1, 3, 5, 7]”}’) AS B,
-> JSON_STORAGE_SIZE(’{“a”: 1000, “b”: “wxyz”, “c”: “[1, 3, 5, 7]”}’) AS C,
-> JSON_STORAGE_SIZE(’[100, “json”, [[10, 20, 30], 3, 5], 425.05]’) AS D;
±—±---±—±---+
| A | B | C | D |
±—±---±—±---+
| 45 | 44 | 47 | 56 |
±—±---±—±---+
1 row in set (0.00 sec)