写在前面

本篇来学习MySQL中的JSON数据类型，这在某些场景下比text类型好用很多。

JSON

JSON 是 JavaScript Object Notation（JavaScript 对象表示法）的缩写，是一个轻量级的，基于文本的，跨语言的数据交换格式，易于阅读和编写。

JSON基本数据类型

JSON 的基本数据类型如下：
（1）数值：十进制数，前面不能有0，可以为负数或小数，也可以为 e 或 E 表示的指数。
（2）字符串：字符串必须用双引号括起来。
（3）布尔值：true，false。
（4）数组：一个由零或多个值组成的有序序列。每个值可以为任意类型。数组使用方括号[] 括起来，元素之间用逗号,分隔。举个例子，如下所示：

1	[1, "abc", null, false, "10:29:06.000000", {"id": 2}]

（5）对象：一个由零或者多个键值对组成的无序集合。其中键必须是字符串，值可以为任意类型。对象使用花括号{}括起来，键值对之间使用逗号,分隔，键与值之间用冒号:分隔。举个例子，如下所示：

1	{"db": ["mysql", "oracle"], "id": 666, "info": {"age": 26}}

（6）空值：null。

创建操作

这里创建一个名为tb_json的表，对应的SQL如下：

1	create table tb_json(t1 json);

直接插入JSON数组

1	insert into tb_json values('[1, "test", null, false, "09:10:11.000000"]');

直接插入JSON对象

1	insert into tb_json values('{"id": 2, "name": "zhangsan", "age": 20}');

使用 JSON_ARRAY 构造JSON数组

JSON_ARRAY(val1,val2,val3…)，用于构造JSON数组。该方法可接受0个或者多个值的列表，并返回一个包含这些值的 JSON 数组，开发者可利用该函数实现将List数组存储到数据库中。
执行结果如下：

使用 JSON_OBJECT 构造JSON对象

JSON_OBJECT(key1,val1,key2,val2…)，用于构造JSON对象。该方法可接受0个或者多个键值对列表，并返回一个包含这些对的 JSON 对象，利用该方法开发者可以很容易的创建一个json对象。

1	select json_object('id', 2, 'name', 'zhangsan', 'age', 20);

执行结果如下：

使用 JSON_QUOTE 将JSON转为字符串

JSON_QUOTE(json_val)，用于将JSON转为字符串。通常用于生成有效的 JSON 字符串文字以包含在 JSON 文档中。

1	select json_quote('[123456]');

执行结果如下：

JSON对象注意点

对于 JSON 对象来说，KEY 名不能重复，具体的需要结合 MySQL 版本来进行说明。
（1）如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原则，会保留第一个 KEY，后面的将被丢弃掉。
（2）而从 MySQL 8.0.3 开始，遵循的是 last duplicate key wins 原则，只会保留最后一个 KEY。

【MySQL 5.7.32】

mysql> select json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
| json_object('key1',10,'key2',20,'key1',30) |
+--------------------------------------------+
| {"key1": 10, "key2": 20}                   |
+--------------------------------------------+
1 row in set (0.02 sec)

【MySQL 8.0.27】

mysql> select json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
| json_object('key1',10,'key2',20,'key1',30) |
+--------------------------------------------+
| {"key1": 30, "key2": 20}                   |
+--------------------------------------------+
1 row in set (0.00 sec)

查询操作

JSON_CONTAINS

JSON_CONTAINS(json_doc, val[, path])，用于查询JSON格式数据中是否包含指定对象，如果包含则返回1，否则返回0。

JSON_CONTAINS_PATH

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)，用于查询JSON格式数据中是否存在指定类型的key，如果包含则返回1，否则返回0。

如果有参数为NULL，那么返回NULL。请注意one_or_all的值只能为”one”或”all”，one表示只要有一个存在即可，all表示所有的都存在才行。

使用如下命令来创建一个测试表并插入一条记录：

1 2	create table tb_json2(t2 json); insert into tb_json2 values('{"empno": 1001, "ename": "jack"}'), ('{"empno": 1002, "ename": "mark"}');

接着我们尝试使用 JSON_CONTAINS_PATH 来查询t2中是否包含 ename 这个key：

1	select json_contains_path(t2,'one','$.ename') from tb_json2;

执行结果如下所示：

JSON_EXTRACT

JSON_EXTRACT(json_doc, path[, path] …)，用于从JSON中抽取指定数据。其中，json_doc 是 JSON 文档，path 是路径。该函数会从 JSON 文档提取指定路径（path）的元素。如果指定 path 不存在，会返回 NULL。注意，可指定多个 path，那么匹配到的多个值会以数组形式返回。

1	select json_extract(t2,'$.ename') from tb_json2;

执行结果如下所示：

JSON_EXTRACT在数组中的使用

我们知道数组的路径是通过下标来表示的，而且第一个元素的下标是 0。

1	select json_extract('[10, 20, [30, 40, 50]]', '$[0]');

执行结果如下所示：

再来看个例子：

1	select json_extract('[10, 20, [30, 40, 50]]', '$[0]','$[1]','$[2][2]');

执行结果如下所示：

当然，我么还可以使用 [M to N] 来获取数组的子集：

1	select json_extract('[10, 20, [30, 40, 50]]', '$[0 to 1]');

执行结果如下所示：

也可以使用last来代表最后一个元素的下标，如下所示：

1	select json_extract('[10, 20, [30, 40, 50]]', '$[last-1 to last]');

执行结果如下所示：

也可通过 [*] 来获取数组中的所有元素，如下所示：

1	select json_extract('[10, 20, [30, 40, 50]]', '$[*]');

执行结果如下所示：

JSON_EXTRACT在对象中的使用

我们知道对象的路径是通过 KEY 来表示的。首先往数据表中定义一个变量：

1	set @j='{"a": 1, "b": [2, 3], "a c": 4}';

之后我们尝试从中获取key为a的值：

1	select json_extract(@j, '$.a');

执行结果如下所示：

再来获取key为b，且数组下标为1的值：

1	select json_extract(@j, '$.b[1]');

执行结果如下所示：

请注意，如果 KEY 在路径表达式中不合法（如存在空格），那么在引用这个 KEY 时，需用双引号括起来：

1	select json_extract(@j, '$."a c"');

执行结果如下所示：

此外，开发者还可以通过 .* 来获取对象中的所有元素：

1	select json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*');

执行结果如下所示：

再来看一个通配符的情况，可以看到这里的 $**.b 会匹配 $.a.b 和 $.c.b 这两种情况：

1	select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');

执行结果如下所示：

column->path

column->path 以及后面提到的 column->>path，它们都是语法糖，在实际使用的时候都会转化为 JSON_EXTRACT。

column->path 等同于 JSON_EXTRACT(column, path) ，注意里面只能指定一个path。

1 2	create table tb_json2(t2 json); insert into tb_json2 values('{"empno": 1001, "ename": "jack"}'), ('{"empno": 1002, "ename": "mark"}');

查看一下t2以及其中ename的信息：

1	select t2 ,t2 -> '$.ename' from tb_json2;

执行结果如下所示：

查看一下empno为1001的用户的信息：

1	select * from tb_json2 where t2 -> '$.empno' = 1001;

执行结果如下所示：

column->>path

和 column->path 类似，只不过它返回的是字符串，请注意，下面三者是等价的：

JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path
1
select t2->'$.ename',json_extract(t2, "$.ename"),json_unquote(t2->'$.ename'),t2->>'$.ename' from tb_json2;
执行结果如下所示：

JSON_SEARCH
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])，该方法用于返回某个字符串（search_str）在 JSON 文档中的位置。其中：
one_or_all：匹配的次数，one 是只匹配一次，all 是匹配所有。如果匹配到多个，结果会以数组的形式返回。
search_str：子串，支持模糊匹配：% 和 _ 。
escape_char：转义符，如果该参数不填或为 NULL，则取默认转义符 \ 。
path：查找路径。
1
select json_search('["abc", [{"k": "1"}, "job"], {"x":"abc"}, {"y":"bcd"}]', 'one', 'abc');
执行结果如下所示：

再来将one修改为all试试：
1
select json_search('["abc", [{"k": "1"}, "job"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc');
执行结果如下所示：

再来尝试寻找一个不存在的值：
1
select json_search('["abc", [{"k": "1"}, "job"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'ddd');
执行结果如下所示：

再来看一个例子，这里我们设置转义符为NULL，查找路径path指定为$[1]，此时代码如下所示：
1
select json_search('["abc", [{"k": "1"}, "job"], {"x":"abc"}, {"y":"bcd"}]', 'all', '%b%', NULL, '$[1]');
执行结果如下所示：

再来看个例子：
1
select json_search('["abc", [{"k": "1"}, "job"], {"x":"abc"}, {"y":"bcd"}]', 'all', '%b%', NULL, '$[3]');
执行结果如下所示：

JSON_KEYS
JSON_KEYS(json_doc[, path])，用于返回 JSON 文档最外层的 key，如果指定了 path，那么返回该 path 对应元素最外层的 key。
1
select json_keys('{"a": 1, "b": {"c": 6}}');
执行结果如下所示：

再来看一个例子：
1
select json_keys('{"a": 1, "b": {"c": 6}}', '$.b');
执行结果如下所示：

JSON_VALUE
JSON_VALUE(json_doc, path)，它是MySQL 8.0.21 版本引入的，用于从 JSON 文档提取指定路径（path）的元素。该函数的完整语法如下：
1
2
3
4
5
6
7
8

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

on_empty:
{NULL | ERROR | DEFAULT value} ON EMPTY

on_error:
{NULL | ERROR | DEFAULT value} ON ERROR
上述命令中各参数的含义如下：
RETURNING type：返回值的类型，不指定，则默认是 VARCHAR(512)。不指定字符集，则默认是 utf8mb4，且区分大小写。
on_empty：如果指定路径没有值，会触发 on_empty 子句，默认是返回 NULL，也可指定 ERROR 抛出错误，或者通过 DEFAULT value 返回默认值。
on_error：三种情况下会触发 on_error 子句：从数组或对象中提取元素时，会解析到多个值；类型转换错误，比如将 “abc” 转换为 unsigned 类型；值被 truncate 了。默认是返回 NULL。

看一个简单的案例：

1	select json_value('{"name": "zhangsan", "age": "26"}', '$.name');

执行结果如下所示：

再来看一个指定RETURNING type的案例：

1	select json_value('{"name": "zhangsan", "weight": "132.82"}', '$.weight' returning decimal(5,2)) as weight;

执行结果如下所示：

再看一个指定on_empty的例子：

1	select json_value('{"name": "zhangsan", "weight": "132.82"}', '$.weight1' error on empty);

执行结果如下所示：

再看一个指定on_error的例子：

1	select json_value('[1, 2, 3, 4]', '$[1 to 2]' error on error);

执行结果如下所示：

再来看一个指定on_error的例子，是因为类型转换错误而导致的：

1	select json_value('{"name": "zhangsan", "weight": "132.82"}', '$.name' returning unsigned error on error) as weight;

执行结果如下所示：

MEMBER OF

value MEMBER OF(json_array)，用于判断 value 是否是 JSON 数组的一个元素，如果是，则返回 1；否则返回0。

1	select 18 member of('[23, "ad", 18, "abc", 16]');

执行结果如下所示：

再来看一个vaue为JOSN数组时的情况：

1	select cast('[1,2]' as json) member of('[[1,2],[3,4]]');

执行结果如下所示：

JSON_OVERLAPS

JSON_OVERLAPS(json_doc1, json_doc2)，它是 MySQL 8.0.17版本引入的，用于比较两个 JSON 文档是否有相同的键值对或数组元素，如果有，则返回 1，否则返回0。如果两个参数都是标量，那么判断这两个标量是否相等。
首先看一下两个JOSN数组是否有相同元素的例子：

1	select json_overlaps('[1,3,5,7,9]', '[3,5,7]');

执行结果如下所示：

看一个两个JOSN数组中没有相同元素的例子：

1	select json_overlaps('[1,3,5,7,9]', '[2,4,6]');

执行结果如下所示：

再来看一个两个JOSN对象是否有相同元素的例子：

1	select json_overlaps('{"a":1,"b":2}', '{"c":3,"d":4,"b":2}');

执行结果如下所示：

再来一个比较简单的例子：

1	select json_overlaps('1', '1'),json_overlaps('1', '2');

执行结果如下所示：

自 MySQL 8.0.17版本开始，InnoDB 支持多值索引，可用在 JSON 数组中。当开发者使用 JSON_CONTAINS、MEMBER OF、JSON_OVERLAPS 进行数组相关的操作时，可使用多值索引来加快查询。

修改操作

JSON_INSERT

JSON_INSERT(json_doc, path, val[, path, val] …)，该方法用于插入新值，注意仅当指定位置或指定 KEY 的值不存在时，才执行插入操作。此外，如果指定的 path 是数组下标，且 json_doc 不是数组，那么该函数首先会将 json_doc 转化为数组，然后再插入新值。

下面通过几个案例来学习如何使用JSON_INSERT这个函数，如下所示：

1	select json_insert('1','$[0]',"6");

执行结果如下所示：

接着我们通过path指定数组下标为1，此时执行如下命令：

1	select json_insert('1','$[1]',"6");

执行结果如下所示：

再来看一个数组的情况：

1	select json_insert('["1","2","3"]','$[3]',"8");

执行结果如下所示：

再来看一下比较综合的案例，如下所示：

1	select json_insert('{ "a": 1, "b": [2, 3]}', '$.a', 6, '$.d', '[true, false]');

执行结果如下所示：

JSON_SET

JSON_SET(json_doc, path, val[, path, val] …)，该方法用于插入新值，并替换已经存在的值。也就是说，如果指定位置或指定 KEY 的值不存在，那么将执行插入操作；如果存在，则执行更新操作。

1	select json_set('{ "a": 1, "b": [2, 3, 4]}', '$.a', 8, '$.c', '[true, false]');

执行结果如下所示：

JSON_REPLACE

JSON_REPLACE(json_doc, path, val[, path, val] …)，该方法用于替换已经存在的值，如果该值不存在那么不会进行替换。

1	select json_replace('{ "a": 1, "b": [2, 3, 4]}', '$.a', 6, '$.c', '[true, false]');

执行结果如下所示：

JSON_ARRAY_APPEND

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)，用于向数组的指定位置追加元素，请注意，如果指定的 path 不存在，那么将不会添加。

1	select json_array_append('["a", ["b", "c"], "d"]', '$[0]', 1);

执行结果如下所示：

再来看一个例子：

1	select json_array_append('["a", ["b", "c"], "d"]', '$[1][0]', 2);

执行结果如下所示：

现在我们尝试修改一个不存在的值：

1	select json_array_append('["a", ["b", "c"], "d"]', '$[3]', 3);

执行结果如下所示：

前面提到的都是JSON数组，接下来我们看一下JSON对象，如下所示：

1	select json_array_append('{"a": 1, "b": [2, 3], "c": 4}', '$.b', 'x', '$', 'z');

执行结果如下所示：

JSON_ARRAY_INSERT

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)，用于向数组的指定位置插入元素。

1	select json_array_insert('["a", ["b", "c"],{"d":"e"}]', '$[0]', 1);

执行结果如下所示：

再来看一个例子：

1	select json_array_insert('["a", ["b", "c"],{"d":"e"}]', '$[1]', cast('[1,2,3]' as json));

执行结果如下所示：

接下来我们尝试往数组中不存在的索引位置处插入一个元素：

1	select json_array_insert('["a", ["b", "c"],{"d":"e"}]', '$[8]', 2);

执行结果如下所示：

可以看到此时即使用户传入的下标超出了数组的索引，但是依旧能插入元素，只是在数组末尾进行了插入。

JSON_MERGE_PATCH

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] …)，它是MySQL 8.0.3版本引入的，用于合并多个 JSON 文档。合并规则如下：
（1）如果两个文档不全是 JSON 对象，那么合并后的结果是第二个文档。
（2）如果两个文档都是 JSON 对象，且不存在同名 KEY，那么合并后的文档包括两个文档的所有元素；如果存在同名 KEY，那么第二个文档的值会覆盖第一个。
首先看第一点，这里我们尝试合并两个JSON数组，如下所示：

1	select json_merge_patch('[1, 2]', '[3, 4]');

执行结果如下所示：

接着我们尝试将一个JOSN数组和一个JSON对象进行合并：

1	select json_merge_patch('[1, 2]', '{"name": "zhangsan"}');

执行结果如下所示：

然后我们尝试合并两个JSON对象，这两个JSON对象不存在同名的Key：

1	select json_merge_patch('{"a": 1}', '{"b": 2}');

执行结果如下所示：

接着我们尝试合并两个JSON对象，注意这两个JSON对象存在同名的Key：

1	select json_merge_patch('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');

执行结果如下所示：

请注意，如果第二个文档中存在 null 值，那么文档合并后不会输出对应的 KEY，这一点需要引起注意：

1	select json_merge_patch('{"a":1, "b":2}', '{"a":3, "b":null}');

执行结果如下所示：

JSON_MERGE_PRESERVE

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] …)，它是自MySQL 8.0.3版本引入的，用于代替 JSON_MERGE。它也是用来合并文档，但合并规则与 JSON_MERGE_PATCH 有所不同，具体如下：
（1）两个文档中，只要有一个文档是数组，那么就会将另一个文档合并到该数组中；
（2）两个文档都是 JSON 对象，如果存在同名 KEY ，那么第二个文档并不会覆盖第一个，而是会将值 append 到第一个文档中。
先看第一点，如下所示的例子：

1	select json_merge_preserve('1','2');

执行结果如下所示：

再来看个例子：

1	select json_merge_preserve('[1, 2]', '[3, 4]');

执行结果如下所示：

再来看一个例子，这个例子看起来更清晰一点：

1	select json_merge_preserve('[1, 2]', '{"a": 1234}'), json_merge_preserve('{"a": 1234}', '[3,4]');

执行结果如下所示：

再来看一个比较复杂的例子：

1	select json_merge_preserve('{"a": 1}', '{"b": 2}'), json_merge_preserve('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');

执行结果如下所示：

JSON_MERGE

JSON_MERGE(json_doc, json_doc[, json_doc] …)，与 JSON_MERGE_PRESERVE 作用一样，不过自 MySQL 8.0.3版本开始就不建议使用，后续会移除。

删除操作

JSON_REMOVE

JSON_REMOVE(json_doc, path[, path] …)，该方法用于删除 JSON 文档中指定位置的元素。

1	select json_remove('{ "a": 1, "b": [2, 3, 4]}', '$.a');

执行结果如下所示：

再来一个删除数组中指定位置元素的例子：

1	select json_remove('["a", ["b", "c"], "d", "e"]', '$[1]');

执行结果如下所示：

再来看一个同时删除对象和数组中指定位置元素的例子：

1	select json_remove('["a", ["b", "c"], "d", "e"]', '$[1]', '$[2]');

请注意，这里指定删除两个位置的元素，相对应的元素索引是在前面操作后结果的基础上，不是在原始JSON文档的索引基础上进行的操作，执行结果如下所示：

辅助函数

JSON_QUOTE

JSON_QUOTE(string)，用于生成有效的 JSON 字符串，主要是对一些特殊字符（如双引号）进行转义。

1	select json_quote('null'), json_quote('"null"'), json_quote('[1, 2, 3]');

执行结果如下所示：

当然了，还可以通过 CAST(value AS JSON) 来进行类型转换。

JSON_UNQUOTE

JSON_UNQUOTE(json_val)，用于将 JSON 转义成字符串输出。举个例子，如下所示：

1	select t2->'$.ename',json_unquote(t2->'$.ename'),json_valid(t2->'$.ename'),json_valid(json_unquote(t2->'$.ename')) from tb_json2;

执行结果如下所示：

从执行结果中看，没使用 JSON_UNQUOTE 函数，那么字符串会用双引号引起来。而加了 JSON_UNQUOTE 函数，字符串就没有用双引号括起来。但本质上，前者是 JSON 中的 STRING 类型，后者是 MySQL 中的字符类型，这一点可通过 JSON_VALID 函数来进行判断。

JSON_OBJECTAGG

JSON_OBJECTAGG(key, value)方法，用于取表中的两列作为参数，其中第一列是 key，第二列是 value，并以JSON 对象进行返回。举个例子，如下所示的数据表：

mysql> select * from tb_json2;
+----------------------------------+------+------+
| t2                               | name | age  |
+----------------------------------+------+------+
| {"empno": 1001, "ename": "jack"} | jack |   20 |
| {"empno": 1002, "ename": "mark"} | mark |   19 |
+----------------------------------+------+------+
2 rows in set (0.00 sec)

之后我们尝试使用 json_objectagg() 方法，将name和age组成一个JOSN对象：

1	select json_objectagg(name, age) from tb_json2;

执行结果如下所示：

JSON_ARRAYAGG

JSON_ARRAYAGG(col_or_expr)方法，用于将列的值聚合成 JSON 数组，请注意 JSON 数组中元素的顺序是随机的。

举个例子，如下所示的数据表：

mysql> select * from tb_json2;
+----------------------------------+------+------+
| t2                               | name | age  |
+----------------------------------+------+------+
| {"empno": 1001, "ename": "jack"} | jack |   20 |
| {"empno": 1002, "ename": "mark"} | mark |   19 |
| {"empno": 1003, "ename": "book"} | book |   22 |
| {"empno": 1004, "ename": "yook"} | yook |   25 |
+----------------------------------+------+------+
4 rows in set (0.00 sec)

之后我们尝试使用 json_arrayagg() 方法，将 age 列的值聚合为一个JOSN数组：

1	select name ,json_arrayagg(age) from tb_json2 group by name;

执行结果如下所示：

JSON_PRETTY

JSON_PRETTY(json_val)方法，用于格式化 JSON 。首先看一个格式化JSON数组的例子，如下所示：

1	select json_pretty('[1,2,3,4,5]');

执行结果如下所示：

再来看一个格式化JSON对象的例子，如下所示：

1	select json_pretty('{"a": 1,"b": 2,"c":3}');

执行结果如下所示：

JSON_STORAGE_FREE

JSON_STORAGE_FREE(json_val)，该方法是MySQL 8.0 新增的，与 Partial Updates 有关，用于计算 JSON 文档在进行部分更新后的剩余空间。

JSON_STORAGE_SIZE

JSON_STORAGE_SIZE(json_val)，该方法是MySQL 5.7.22 引入的，用于计算 JSON 文档的空间使用情况。

JSON_DEPTH

JSON_DEPTH(json_doc)，该方法用于返回 JSON 文档的最大深度。请注意，对于空数组，空对象，标量值，其深度为 1。

1	select json_depth('{}'), json_depth('[1,2,3]'), josn_depth('[1,{"a": 2}]');

执行结果如下所示：

JSON_LENGTH

JSON_LENGTH(json_doc[, path])，该方法用于返回 JSON 文档的长度，计算规则如下：
（1）如果是标量值，其长度为 1；
（2）如果是数组，其长度为数组元素的个数；
（3）如果是对象，其长度为对象元素的个数；
（4）不包括嵌套数据和嵌套对象的长度。
接下来我们通过案例来演示上述计算规则的使用。首先是标量值：

1	select json_length('"abcd"');

执行结果如下所示：

看一个纯数组的例子：

1	select json_length('[1,2,3]');

执行结果如下所示：

看一个纯对象的例子：

1	select json_length('{"name": "zhangsan", "sex": "female"}');

执行结果如下所示：

来看一个数组和对象混用的例子，注意它不包括嵌套数据和嵌套对象的长度：

1	select json_length('[1,2,3,{"name": "wanger", "sex": "male"}]');

执行结果如下所示：

再看一个对象和对象混用的例子，注意它同样不包括嵌套数据和嵌套对象的长度：

1	select json_length('{"name":"zhangsan","hobby":{"language": "english","boll":"footboll"}}');

执行结果如下所示：

我们尝试输出hobby这个对象的长度，如下所示：

1	select json_length('{"name":"zhangsan","hobby":{"language": "english","boll":"footboll"}}','$.hobby');

执行结果如下所示：

JSON_TYPE

JSON_TYPE(json_val)，该方法用于返回 JSON 值的类型。首先看一个JSON整型的例子：

1	select json_type('12345');

执行结果如下所示：

再看一个JSON字符串类型的例子：

1	select json_type('"abcd"');

执行结果如下所示：

再看一个JOSN时间类型的例子：

1	select json_type(cast(now() as json));

执行结果如下所示：

再看一个JOSN数组类型的例子：

1	select json_type(json_extract('{"a": [1234, false, "1234"]}', '$.a'));

执行结果如下所示：

JSON_VALID

JSON_VALID(val)，该方法用于判断给定值是否为有效的 JSON 文档。举个例子，如下所示：

1	select json_valid('hello'), json_valid('"hello"');

执行结果如下所示：

JSON_TABLE

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)，该方法用于从 JSON 文档中提取数据并以表格形式返回。

该方法的完整语法如下：

JSON_TABLE(
    expr,
    path COLUMNS (column_list)
)   [AS] alias

column_list:
    column[, column][, ...]

column:
    name FOR ORDINALITY
    |  name type PATH string_path [on_empty] [on_error]
    |  name type EXISTS PATH string_path
    |  NESTED [PATH] path COLUMNS (column_list)

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR

解释一下上述各个参数的含义：

expr：可返回 JSON 文档的表达式。它可以是一个标量（ JSON 文档），列名或者一个函数调用（ JSON_EXTRACT(t1.json_data,’$.post.comments’) ）。
path：JSON 的路径表达式。
column：列的类型，支持以下四种类型：
- name FOR ORDINALITY：序号。name 是列名。
- name type PATH string_path [on_empty] [on_error]：提取指定路径（ string_path ）的元素。name 是列名，type 是 MySQL 中的数据类型。
- name type EXISTS PATH string_path：指定路径（ string_path ）的元素是否存在。
- NESTED [PATH] path COLUMNS (column_list)：将嵌套对象或数组与来自父对象或数组的 JSON 值扁平化为一行输出。

举个例子，如下所示：

SELECT
	* 
FROM
	json_table (
		'[{"x":2, "y":"8", "z":9, "b":[1,2,3]}, {"x":"3", "y":"7"}, {"x":"4", "y":6, "z":10}]',
		"$[*]" COLUMNS (
			id FOR ordinality,
			xval VARCHAR ( 100 ) path "$.x",
			yval VARCHAR ( 100 ) path "$.y",
			z_exist INT EXISTS path "$.z",
			nested path '$.b[*]' COLUMNS ( b INT PATH '$' ) 
		) 
	) AS t;

执行结果如下所示：

JSON_SCHEMA_VALID

JSON_SCHEMA_VALID(schema,document)，该方法用于判断 document （ JSON 文档）是否满足 schema （ JSON 对象）定义的规范要求。完整的规范要求可参考 Draft 4 of the JSON Schema specification。如果不满足，可通过 JSON_SCHEMA_VALIDATION_REPORT() 方法来获取具体的原因。

举个例子，如下所示的 schema 信息如下：

set @schema = '{
   "type": "object",
   "properties": {
     "latitude": {
       "type": "number",
       "minimum": -90,
       "maximum": 90
     },
     "longitude": {
       "type": "number",
       "minimum": -180,
       "maximum": 180
     }
   },
   "required": ["latitude", "longitude"]
}';

从 schema 中可以看出如下信息：
（1）document 必须是 JSON 对象；
（2）JSON 对象必须有 latitude 和 longitude 这两个属性；
（3）latitude 和 longitude 必须是数值类型，且两者的大小分别在 -90 ～ 90，-180 ～ 180 之间。
那么接下来我们就通过定义一个document，并通过 json_schema_valid() 方法来验证document 是否满足 schema 定义的规范要求，如下所示：

mysql> set @schema = '{
    '>    "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.564456,"longitude": 10.237812}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_schema_valid(@schema, @document);
+---------------------------------------+
| json_schema_valid(@schema, @document) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

从上面执行结果中可以看出，验证是通过的。接下来我们尝试定义一个 document ，它只传入latitude这一个参数，可以看到验证是不通过的：

mysql> set @document = '{"latitude": 63.564456}';
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)

之后我们尝试输出一下错误信息：

mysql> select json_pretty(json_schema_validation_report(@schema, @document))\G;
*************************** 1. row ***************************
json_pretty(json_schema_validation_report(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'",
  "schema-location": "#",
  "document-location": "#",
  "schema-failed-keyword": "required"
}
1 row in set (0.00 sec)

ERROR:
No query specified

可以看到这里的原因就是缺少longitude参数，那么接下来我们尝试传入longitude参数，但是取值我们又取错误的值，然后再来测试一下：

mysql> set @document = '{"latitude": 96, "longitude": 0}';
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)

mysql> select json_pretty(json_schema_validation_report(@schema, @document))\G;
*************************** 1. row ***************************
json_pretty(json_schema_validation_report(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#/latitude' failed requirement 'maximum' at JSON Schema location '#/properties/latitude'",
  "schema-location": "#/properties/latitude",
  "document-location": "#/latitude",
  "schema-failed-keyword": "maximum"
}
1 row in set (0.00 sec)

ERROR:
No query specified

从执行结果中也可以看到，验证不通过的原因就是取值超出了属性的可允许取值范围。

对JSON字段创建索引

实际上，和 TEXT，BLOB 字段一样，JSON 字段是不允许直接创建索引的：

1 2	mysql> create table tb_json3(t1 json, index(t1)); ERROR 3152 (42000): JSON column 't1' supports indexing only via generated columns on a specified JSON path.

实际上即使支持直接创建索引，其意义也不大，一般来说我们都是基于文档中的元素进行查询，很少会基于整个 JSON 文档。如果需要对文档中的元素进行查询，那么就需要使用到 MySQL 5.7 引入的虚拟列及函数索引。

如下所示，我们新建一个数据表tb_json3，其中t1类型为JOSN，然后t2是虚拟列，之后使用 index (t2) 来对虚拟列t2添加索引：

1	create table tb_json3(t1 json, t2 varchar(100) as (JSON_UNQUOTE(t1 -> '$.name')), index (t2));

之后我们往该数据表中插入5条测试数据：

INSERT INTO tb_json3 ( t1 )
VALUES
	( '{"id": 1, "name": "zhangsan"}' ),
	( '{"id": 2, "name": "lisi"}' ),
	( '{"id": 3, "name": "wanger"}' ),
	( '{"id": 4, "name": "mazi"}' ),
	( '{"id": 5, "name": "zhangsanfeng"}' )
;

可以看到此时数据库中的数据如下所示：

接下来我们尝试查询姓名为lisi的用户，首先使用虚拟列来进行查询，查看一下执行计划：

1	explain select * from tb_json3 where t2 = 'lisi';

执行一下该执行计划，结果如下所示：

接着我们尝试使用文档中的元素来查询，同样也是先看一下执行计划：

1	explain select * from tb_json3 where t1 -> '$.name' = 'lisi';

执行一下该执行计划，结果如下所示：

从上述输出结果中可以看到，无论是使用虚拟列，还是文档中的元素来进行查询，它们都会用到索引。请注意，在创建虚拟列时，需要使用 JSON_UNQUOTE方法，来将 t1 -> “$.name” 的返回值转换为字符串。

将存储 JSON 字符串的字符字段升级为 JSON 字段

在 MySQL 支持 JSON 类型之前，对于 JSON 文档，一般是以字符串的形式存储在字符类型中（VARCHAR 或 TEXT）。

现在问题来了，在 JSON 类型出来后，如何将这些字符字段升级为 JSON 字段呢？为了学习的需要，这里先构建测试表和数据：


create table tb_json4 (id int auto_increment primary key, t1 text);

insert into tb_json4 (t1) values ('{"id": "1", "name": "a"}'),
('{"id": "2", "name": "b"}'), 
('{"id": "3", "name": "c"}'), 
('{"id", "name": "d"}');

注意，第4条记录的json数据是有问题的，不符合JSON格式的要求，这里先不管，后面会进行修改，此时数据库中的数据如下所示：

接着，修改t1字段的类型。如果我们直接使用 DDL语句来修改字段的数据类型，那么它会报错：

1	alter table tb_json4 modify t1 json;

执行结果如下所示：

所以我们需要另一种方式来修改t1字段的类型，对应的修改步骤如下所示：
第一步，使用 json_valid 方法找出不满足 JSON 格式要求的文档：

1	select * from tb_json4 where json_valid(t1) = 0;

执行结果如下所示：

第二步，处理不满足 JSON 格式要求的文档。接着我们就需要使用DDL语句来修改不满足JSON格式要求的文档：

1	update tb_json4 set t1 = '{"id": "4", "name": "d"}' where id = 4;

执行结果如下所示：

第三步，将 TEXT 字段修改为 JSON 字段。现在我们再次检查是否存在不符合JSON格式要求的文档，执行结果如下所示：

之后我们就可以修改t1字段的类型了：

1	alter table tb_json4 modify t1 json;

执行结果如下所示：

使用JSON时的注意事项

在使用JSON类型时，有如下四个注意事项：
（1）在 MySQL 8.0.13 之前，不允许对 BLOB，TEXT，GEOMETRY，JSON 字段设置默认值。而从 MySQL 8.0.13 开始，取则消了这个限制。

请注意，在设置默认值时，默认值需要由小括号 () 括起来，否则会抛出 JSON 字段不允许设置默认值的提示：

mysql> create table tb_json5(t1 json not null default '');
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 't1' can't have a default value

mysql> create table tb_json5(t1 json not null default (''));
Query OK, 0 rows affected (0.01 sec)

（2）不允许直接给JSON类型字段创建索引，但可创建函数索引；
（3）JSON 列的最大大小和 LONGBLOB（LONGTEXT）一样，都是 4G；
（4）插入时，单个文档的大小受到 max_allowed_packet 的限制，该参数最大是 1G。

Partial Updates

在 MySQL 5.7 中，对于JSON 文档进行更新的策略：先删除旧的文档，再插入新的文档。这种方式效率非常低下，即使这个修改很微小，只涉及几个字节，也会替换掉整个文档。
在
而在 MySQL 8.0 中，针对 JSON 文档，引入了一项新的特性—Partial Updates（部分更新），它支持 JSON 文档的原地更新。得益于这个特性，JSON 文档的处理性能得到了极大提升。

如下所示，我们新建一个数据表tb_json6，其中id类型为int，而t1为JSON类型，对应的建表语句如下所示：

1	create table tb_json6(id int auto_increment primary key, t1 json);

之后我们往该数据表中插入5条测试数据：

INSERT INTO tb_json6 ( t1 )
VALUES
	( '{"id": 1, "name": "zhangsan"}' ),
	( '{"id": 2, "name": "lisi"}' ),
	( '{"id": 3, "name": "wanger"}' ),
	( '{"id": 4, "name": "mazi"}' ),
	( '{"id": 5, "name": "zhangsanfeng"}' )
;

可以看到此时数据库中的数据如下所示：

使用 Partial Updates 的条件

（1）被更新的列是 JSON 类型。
（2）使用 JSON_SET，JSON_REPLACE，JSON_REMOVE 这三个函数进行 UPDATE 操作。举个例子，如下所示更新语句：

1	update tb_json6 set t1=json_remove(t1,'$.id') where id=1;

执行该语句后，我们查询一下数据表，结果如下所示：

请注意，如果开发者不使用上述三个函数赋值，而采用手动显式赋值，那么就不会进行部分更新。举个例子，如下所示：

1	update tb_json6 set t1='{"id": 1, "name": "a"}' where id=1;

执行该语句后，我们查询一下数据表，结果如下所示：

实际上此时它并不是进行部分更新，而是先删除旧的记录，然后再插入了新的记录，这个操作在数据量大的情况下会影响系统性能。
（3）输入列和目标列必须是同一列，否则也不会进行部分更新。举个例子，如下所示：

1	update tb_json6 set t1 = json_replace(t1,'$.id',10) where id=1;

执行该语句后，我们查询一下数据表，结果如下所示：

如果我们使用的是下面的语句，那么就不是部分更新了，这里的t2列实际上是不存在的，这里只是为了演示：

1	update tb_json6 set t1 = json_replace(t2,'$.id',10) where id=1;

（4）变更前后，JSON 文档的空间使用不会增加。举个例子，首先我们查看一下id为1的记录所占用的空间大小：

1	select *,json_storage_size(t1),json_storage_free(t1) from tb_json6 where id=1;

执行结果如下所示：

接着我们尝试修改id为1的记录，将id为1的记录中的id属性给去除，对应的SQL如下所示：

1	update tb_json6 set t1=json_remove(t1,'$.id') where id=1;

之后我们再次执行查询语句：

1	select *,json_storage_size(t1),json_storage_free(t1) from tb_json6 where id=1;

可以看到执行结果如下所示：

接下来我们使用json_set函数来更新JSON文档，对应的SQL如下所示：

1	update tb_json6 set t1=json_set(t1,'$.id',10086) where id=1;

之后我们再次执行查询语句：

1	select *,json_storage_size(t1),json_storage_free(t1) from tb_json6 where id=1;

可以看到执行结果如下所示：

接这我们再次使用json_set函数来更新JSON文档，对应的SQL如下所示：

1	update tb_json6 set t1=json_set(t1,'$.id','helloworld') where id=1;

之后再次执行查询语句，可以看到执行结果如下所示：

怎么两次使用json_set函数查询出来的数据有些不一样呢？下面我们将对这些现象进行详细学习。

在上面的例子中，我们使用了json_storage_size和json_storage_free这两个函数，其中json_storage_size用于获取JSON文档的空间使用情况，而json_storage_free则是用于获取JSON文档在执行原地更新后的空间释放情况。

从例子中可以看出，我们一共执行了三次更新操作（两次json_set，一次json_remove），其中前两次是原地更新，而第三次不是。那么问题来了，同样使用了json_set方法，为什么第一次是原地更新，而第二次却不是呢？那是因为第一次的json_set复用了json_remove释放的空间，而第二次的json_set执行的是更新操作，且 ‘helloworld’ 比 10086 需要更多的存储空间，因此两次查询获取到的空间使用情况就不一样了。

如何在 binlog 中开启 Partial Updates

Partial Updates 不仅适用于存储引擎层，还可用于主从复制。主从复制场景下，开启 Partial Updates，只需将binlog_row_value_options参数设置为 PARTIAL_JSON，注意这个参数默认为空。

下面我们将通过一个案例来学习，同一个 UPDATE 操作，在开启和不开启 Partial Updates情况下，在 binlog 中的记录有什么区别。

这是UPDATE操作所使用的语句，如下所示：

1	update tb_json6 set t1 = json_replace(t1,'$.id',10) where id = 1;

以下是未开启Partial Updates时的binlog记录：

### UPDATE `slowtech`.`tb_json6`
### WHERE
###   @1=1
###   @2='{"id": "1", "name": "a"}'
### SET
###   @1=1
###   @2='{"id": 10, "name": "a"}'

下面是开启了Partial Updates时的binlog记录：

### UPDATE `slowtech`.`tb_json6`
### WHERE
###   @1=1
###   @2='{"id": 1, "name": "a"}'
### SET
###   @1=1
###   @2=JSON_REPLACE(@2, '$.id', 10)

通过对上述两条binlog记录的对比，可以看出在不开启Partial Updates时，无论是修改前的镜像（before_image）还是修改后的镜像（after_image），记录的都是完整文档。而开启Partial Updates后，对于修改后的镜像，记录的是命令，而不是完整文档，这样可节省近一半的空间。

开发者在将 binlog_row_value_options 参数设置为 PARTIAL_JSON 后，对于可使用 Partial Updates 的操作，在 binlog 中，不再通过 ROWS_EVENT 来记录，而是新增了一个 PARTIAL_UPDATE_ROWS_EVENT 的事件类型。

请注意，binlog 中使用 Partial Updates，只需满足存储引擎层使用 Partial Updates 的前三个条件，无需考虑变更前后，JSON 文档的空间使用是否会增加。

关于 Partial Updates 的性能测试

首先我们构造数据表和数据，假设tb_json7表一共有 16 个文档，每个文档近 10 MB，对应SQL语句如下所示：

create table tb_json7(id int auto_increment primary key,
               t1 json,
               name varchar(100) as (t1->>'$.name'),
               age int as (t1->'$.age'));

insert into tb_json7(t1) values
(json_object('name', 'Joe', 'age', 24,
             'data', repeat('x', 10 * 1000 * 1000))),
(json_object('name', 'Sue', 'age', 32,
             'data', repeat('y', 10 * 1000 * 1000))),
(json_object('name', 'Pete', 'age', 40,
             'data', repeat('z', 10 * 1000 * 1000))),
(json_object('name', 'Jenny', 'age', 27,
             'data', repeat('w', 10 * 1000 * 1000)));

insert into tb_json7(t1) select t1 from tb_json7;
insert into tb_json7(t1) select t1 from tb_json7;

接着我们尝试测试如下SQL语句：

1	update tb_json7 set t1 = json_set(t1, '$.age', age + 1);

然后在如下四种场景进行执行，并统计对应的执行时间：
（1）MySQL 5.7.36；
（2）MySQL 8.0.27
（3）MySQL 8.0.27，binlog_row_value_options=PARTIAL_JSON
（4）MySQL 8.0.27，binlog_row_value_options=PARTIAL_JSON + binlog_row_image=MINIMAL
这里我们选择分别执行 10 次，然后去掉最大值和最小值，最后求平均值，得到的结果如下所示：

请注意，这里我们以 MySQL 5.7.36 的查询时间作为基准：
（1）MySQL 8.0 只开启存储引擎层的 Partial Updates，查询时间比 MySQL 5.7 快 1.94 倍。
（2）MySQL 8.0 同时开启存储引擎层和 binlog 中的 Partial Updates，查询时间比 MySQL 5.7 快 4.87 倍。
（3）如果在（2）的基础上，同时将 binlog_row_image 设置为 MINIMAL，查询时间更是比 MySQL 5.7 快 102.22 倍。
当然，在生产环境，我们一般很少将 binlog_row_image 设置为 MINIMAL。但即便如此，只开启存储引擎层和 binlog 中的 Partial Updates，查询时间也比 MySQL 5.7 快 4.87 倍，这样看性能提升还是比较明显。

参考文章：

写在前面

JSON

JSON基本数据类型

创建操作

直接插入JSON数组

直接插入JSON对象

使用 JSON_ARRAY 构造JSON数组

使用 JSON_OBJECT 构造JSON对象

使用 JSON_QUOTE 将JSON转为字符串

JSON对象注意点

查询操作

JSON_CONTAINS

JSON_CONTAINS_PATH

JSON_EXTRACT

JSON_EXTRACT在数组中的使用

JSON_EXTRACT在对象中的使用

column->path

column->>path

JSON_SEARCH

JSON_KEYS

JSON_VALUE

MEMBER OF

JSON_OVERLAPS

修改操作

JSON_INSERT

JSON_SET

JSON_REPLACE

JSON_ARRAY_APPEND

JSON_ARRAY_INSERT

JSON_MERGE_PATCH

JSON_MERGE_PRESERVE

JSON_MERGE

删除操作

JSON_REMOVE

辅助函数

JSON_QUOTE

JSON_UNQUOTE

JSON_OBJECTAGG

JSON_ARRAYAGG

JSON_PRETTY

JSON_STORAGE_FREE

JSON_STORAGE_SIZE

JSON_DEPTH

JSON_LENGTH

JSON_TYPE

JSON_VALID

JSON_TABLE

JSON_SCHEMA_VALID

对JSON字段创建索引

将存储 JSON 字符串的字符字段升级为 JSON 字段

使用JSON时的注意事项

Partial Updates

使用 Partial Updates 的条件

如何在 binlog 中开启 Partial Updates

关于 Partial Updates 的性能测试