侧边栏壁纸
博主头像
孔子说JAVA博主等级

成功只是一只沦落在鸡窝里的鹰,成功永远属于自信且有毅力的人!

  • 累计撰写 297 篇文章
  • 累计创建 134 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

MySql8中常用JSON函数使用实战

孔子说JAVA
2022-07-11 / 0 评论 / 0 点赞 / 97 阅读 / 36,446 字 / 正在检测是否收录...

MySQL从5.7.8后引入了一种新的数据类型-json,可以有效的访问json格式的数据,至此MySql有了非关系型数据的访问能力。而从MYSQL 8.0开始除了提供JSON 数据类型,还有一组 SQL 函数可用于操作 JSON 类型的值,例如创建JSON对象、增删改查JSON数据中的某个元素。

image-1657425459157

1、JSON函数介绍

MySql5.7开始引入的json类型基础操作实战教程 中,我们查询的json都是整条json数据,这样看起来不是很方便,那么如果我们只想看json中的某个字段怎么办?这就需要使用MYSQL提供的函数:json_extract(字段名,json字段名),我们可以先看看官网的函数介绍:

image-1657427059416

从上图可以看到json_extract()函数:Return data from JSON document,表示从json文档中返回指定的数据(字段)。

这里简单了解下路径语法

  • .keyName:JSON对象中键名为keyName的值;
  • 对于不合法的键名(如有空格),在路径引用中必须用双引号"将键名括起来,例,.“key name”;
  • [index]:JSON数组中索引为index的值,JSON数组的索引同样从0开始;
  • [index1 to index2]:JSON数组中从index1到index2的值的集合;
  • .*: JSON对象中的所有value;
  • [*]: JSON数组中的所有值;
  • prefix**suffix: 以prefix开头并以suffix结尾的路径;
  • **.keyName 为多个路径,如对于JSON对象{"a": {"b": 1}, "c": {"b": 2}} , $**.b 指路径 $.a.b$.c.b
  • 不存在的路径返回结果为NULL;
  • 前导$字符表示当前正在使用的JSON文档

例子:

对于数组[3, {"a": [5, 6], "b": 10}, [99, 100]]

$[1]为{"a": [5, 6], "b": 10}。
[1].a为[5, 6]。
$[1].a[1]为 6。
$[1].b为 10。
$[2][0]为 99。

2、JSON表及数据准备

含有JSON类型的表

CREATE TABLE `dept` (
  `id` int(11) NOT NULL,
  `dept` varchar(255) DEFAULT NULL,
  `json_value` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

测试数据

insert into dept VALUES(1,'部门1','{"deptName": "部门1", "deptId": "1", "deptLeaderId": "3"}');
insert into dept VALUES(2,'部门2','{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}');
insert into dept VALUES(3,'部门3','{"deptName": "部门3", "deptId": "3", "deptLeaderId": "5"}');
insert into dept VALUES(4,'部门4','{"deptName": "部门4", "deptId": "4", "deptLeaderId": "5"}');
insert into dept VALUES(5,'部门5','{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}');

3、JSON函数用法实战

3.1 创建函数

3.1.1 JSON_ARRAY函数

JSON_ARRAY:创建JSON数组(生成一个包含指定元素的json数组)。

  • 用法:JSON_ARRAY([val[, val] …])

例1:

mysql>SELECT JSON_ARRAY(1,"abc",NULL,TRUE, CURTIME());

+---------------------------------------------+
| JSON_ARRAY(1,"abc",NULL,TRUE, CURTIME()) |
+---------------------------------------------+
| [1,"abc",null,true,"11:30:24.000000"]   |
+---------------------------------------------+

例2: 如我们添加这么一组数据到dept表中:

insert into dept VALUES(7,'部门9','{"deptName": ["1","2","3"], "deptId": "5", "deptLeaderId": "5"}');
insert into dept VALUES(7,'部门9','{"deptName": ["5","6","7"], "deptId": "5", "deptLeaderId": "5"}');

我们要查询deptName包含1的数据:

SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1"))

image-1657427755364

3.1.2 JSON_OBJECT函数

JSON_OBJECT:将一个键值对列表转换成json对象。

  • 生成一个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数,则抛错。
  • 用法1:JSON_OBJECT(key1,val1,key2,val2…)
  • 用法2:JSON_OBJECT([key, val[, key, val] …])

例1:

mysql>SELECT JSON_OBJECT('id', 87,'name','carrot');

+-----------------------------------------+
| JSON_OBJECT('id', 87,'name','carrot') |
+-----------------------------------------+
| {"id": 87,"name":"carrot"}            |
+-----------------------------------------+

例2: 如我们想查询某个对象里面的值等于多少,可以添加这么一组数据到dept表中:

insert into dept VALUES(6,'部门9','{"deptName": {"dept":"de","depp":"dd"}, "deptId": "5", "deptLeaderId": "5"}');

从上述脚本中可以看到deptName中还有一个对象,里面还有dept和depp两个属性字段,那么我们应该怎么查询depp=dd的员工呢。

SELECT * from (
SELECT *,json_value->'$.deptName' as deptName FROM dept
) t WHERE JSON_CONTAINS(deptName,JSON_OBJECT("depp","dd"));

image-1657427644091

3.1.3 JSON_QUOTE函数

JSON_QUOTE:将json_val用"号括起来。

  • 用法:JSON_QUOTE(json_val)

例1:

mysql>SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');

+--------------------+----------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"null"') |
+--------------------+----------------------+
|"null"             |"\"null\""           |
+--------------------+----------------------+

mysql>SELECT JSON_QUOTE('[1, 2, 3]');
+-------------------------+
| JSON_QUOTE('[1, 2, 3]') |
+-------------------------+
|"[1, 2, 3]"             |
+-------------------------+

3.1.4 CONVERT函数

CONVERT:将json字符串转化为JSON格式。

  • 用法:CONVERT(json_string,JSON)

例1:

mysql>select CONVERT('{"mail": "amy@gmail.com", "name": "Amy"}',JSON);
+----------------------------------------------------------+
|CONVERT('{"mail": "amy@gmail.com", "name": "Amy"}',JSON) |
+----------------------------------------------------------+
| {"mail":"amy@gmail.com","name":"Amy"}                 |
+----------------------------------------------------------+

3.2 查询函数

3.2.1 JSON_EXTRACT函数

json_extract():从json中返回想要的字段

  • 从json文档里抽取数据。如果有参数有NULL或path不存在,则返回NULL。如果抽取出多个path,则返回的数据封闭在一个json array里。

  • 用法1:json_extract(字段名, $.json字段名)

  • 用法2:JSON_EXTRACT(json_doc, path[, path] …)

例1:

select id,json_extract(json_value,'$.deptName') as deptName from dept;

image-1657427382246

比如我们要查询deptName like ‘部门’ 的数据

SELECT * FROM dept WHERE JSON_EXTRACT(json_value,'$.deptName') like '%部门%';

image-1657427973792

例2:

mysql>SELECT JSON_EXTRACT('[10, 20, [30, 40]]','$[1]');
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]','$[1]') |
+--------------------------------------------+
| 20                                         |
+--------------------------------------------+
mysql>SELECT JSON_EXTRACT('[10, 20, [30, 40]]','$[1]','$[0]');
+----------------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]','$[1]','$[0]') |
+----------------------------------------------------+
| [20, 10]                                           |
+----------------------------------------------------+
mysql>SELECT JSON_EXTRACT('[10, 20, [30, 40]]','$[2][*]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]','$[2][*]') |
+-----------------------------------------------+
| [30, 40]                                      |
+-----------------------------------------------+

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

mysql>SELECT c, JSON_EXTRACT(c,"$.id"), g
     >FROM jemp
     >WHERE JSON_EXTRACT(c,"$.id") > 1
     >ORDER BY JSON_EXTRACT(c,"$.name");
+-------------------------------+-----------+------+
| c                             | c->"$.id" | g    |
+-------------------------------+-----------+------+
| {"id":"3","name":"Barney"} |"3"       |    3 |
| {"id":"4","name":"Betty"}  |"4"       |    4 |
| {"id":"2","name":"Wilma"}  |"2"       |    2 |
+-------------------------------+-----------+------+
3rows in set (0.00 sec)
 
mysql>SELECT c, c->"$.id", g
     >FROM jemp
     >WHERE c->"$.id" > 1
     >ORDER BY c->"$.name";
+-------------------------------+-----------+------+
| c                             | c->"$.id" | g    |
+-------------------------------+-----------+------+
| {"id":"3","name":"Barney"} |"3"       |    3 |
| {"id":"4","name":"Betty"}  |"4"       |    4 |
| {"id":"2","name":"Wilma"}  |"2"       |    2 |
+-------------------------------+-----------+------+
3rows in set (0.00 sec)

在MySQL 5.7.13+,还可以用"->>"表示去掉抽取结果的"号,下面三种效果是一样的:

  • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
  • JSON_UNQUOTE(column -> path)
  • column->>path
mysql>SELECT *FROM jempWHERE g > 2;
+-------------------------------+------+
| c                             | g    |
+-------------------------------+------+
| {"id":"3","name":"Barney"} |    3 |
| {"id":"4","name":"Betty"}  |    4 |
+-------------------------------+------+
2rows in set (0.01 sec)
 
mysql>SELECT c->'$.name' AS name    
    ->    FROM jempWHERE g > 2;
+----------+
|name     |
+----------+
|"Barney" |
|"Betty"  |
+----------+
2rows in set (0.00 sec)
 
mysql>SELECT JSON_UNQUOTE(c->'$.name')AS name
    ->    FROM jempWHERE g > 2;
+--------+
|name   |
+--------+
| Barney |
| Betty  |
+--------+
2rows in set (0.00 sec)
 
mysql>SELECT c->>'$.name' AS name
    ->    FROM jempWHERE g > 2;
+--------+
|name   |
+--------+
| Barney |
| Betty  |
+--------+
2rows in set (0.00 sec)

3.2.2 JSON_CONTAINS函数

JSON_CONTAINS():JSON格式数据是否在字段中包含特定对象

  • 查询json文档是否在指定path包含指定的数据,包含则返回1,否则返回0。如果有参数为NULL或path不存在,则返回NULL。

  • 用法: JSON_CONTAINS(target, candidate[, path])

例1:

mysql>SET @j ='{"a": 1, "b": 2, "c": {"d": 4}}';
mysql>SET @j2 ='1';
mysql>SELECT JSON_CONTAINS(@j, @j2,'$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2,'$.a') |
+-------------------------------+
|                             1 |
+-------------------------------+
mysql>SELECT JSON_CONTAINS(@j, @j2,'$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2,'$.b') |
+-------------------------------+
|                             0 |
+-------------------------------+
 
mysql>SET @j2 ='{"d": 4}';
mysql>SELECT JSON_CONTAINS(@j, @j2,'$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2,'$.a') |
+-------------------------------+
|                             0 |
+-------------------------------+
mysql>SELECT JSON_CONTAINS(@j, @j2,'$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2,'$.c') |
+-------------------------------+
|                             1 |
+-------------------------------+

例2:

-- 查询包含deptName=部门5的对象
select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))

3.2.3 JSON_CONTAINS_PATH函数

JSON_CONTAINS_PATH():查询是否存在指定路径,存在则返回1,否则返回0。如果有参数为NULL,则返回NULL。

  • one_or_all只能取值"one"或"all",one表示只要有一个存在即可;all表示所有的都存在才行。

  • 用法: JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)

例1:

mysql>SET @j ='{"a": 1, "b": 2, "c": {"d": 4}}';
mysql>SELECT JSON_CONTAINS_PATH(@j,'one','$.a','$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j,'one','$.a','$.e') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
mysql>SELECT JSON_CONTAINS_PATH(@j,'all','$.a','$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j,'all','$.a','$.e') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
mysql>SELECT JSON_CONTAINS_PATH(@j,'one','$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j,'one','$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
mysql>SELECT JSON_CONTAINS_PATH(@j,'one','$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j,'one','$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+

3.2.4 JSON_KEYS函数

JSON_KEYS():JSON文档中的键数组。

  • 获取json文档在指定路径下的所有键值,返回一个json array。如果有参数为NULL或path不存在,则返回NULL。
  • 用法1: JSON_KEYS(json_value)
  • 用法2: JSON_KEYS(json_doc[, path])

例1:

mysql>SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a","b"]                            |
+---------------------------------------+
mysql>SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}','$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}','$.b') |
+----------------------------------------------+
| ["c"]                                        |
+----------------------------------------------+

例2: 如我们想查询json格式数据中的所有key:

SELECT JSON_KEYS(json_value) FROM dept 

image-1657428045301

3.2.5 JSON_SEARCH函数

JSON_SEARCH():用于在json格式中查询并返回符合条件的节点,是使用频率非常高的一个函数。

  • 查询包含指定字符串的paths,并作为一个json array返回。如果有参数为NUL或path不存在,则返回NULL。
  • one_or_all:"one"表示查询到一个即返回;"all"表示查询所有。
  • search_str:要查询的字符串。可以用LIKE里的 %_ 匹配。
  • path:在指定path下查。
  • 用法: JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])

例1:

mysql>SET @j ='["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
 
mysql>SELECT JSON_SEARCH(@j,'one','abc');
+-------------------------------+
| JSON_SEARCH(@j,'one','abc') |
+-------------------------------+
|"$[0]"                        |
+-------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','abc');
+-------------------------------+
| JSON_SEARCH(@j,'all','abc') |
+-------------------------------+
| ["$[0]","$[2].x"]            |
+-------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','ghi');
+-------------------------------+
| JSON_SEARCH(@j,'all','ghi') |
+-------------------------------+
|NULL                          |
+-------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','10');
+------------------------------+
| JSON_SEARCH(@j,'all','10') |
+------------------------------+
|"$[1][0].k"                  |
+------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','10',NULL,'$');
+-----------------------------------------+
| JSON_SEARCH(@j,'all','10',NULL,'$') |
+-----------------------------------------+
|"$[1][0].k"                             |
+-----------------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','10',NULL,'$[*]');
+--------------------------------------------+
| JSON_SEARCH(@j,'all','10',NULL,'$[*]') |
+--------------------------------------------+
|"$[1][0].k"                                |
+--------------------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','10',NULL,'$**.k');
+---------------------------------------------+
| JSON_SEARCH(@j,'all','10',NULL,'$**.k') |
+---------------------------------------------+
|"$[1][0].k"                                 |
+---------------------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','10',NULL,'$[*][0].k');
+-------------------------------------------------+
| JSON_SEARCH(@j,'all','10',NULL,'$[*][0].k') |
+-------------------------------------------------+
|"$[1][0].k"                                     |
+-------------------------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','10',NULL,'$[1]');
+--------------------------------------------+
| JSON_SEARCH(@j,'all','10',NULL,'$[1]') |
+--------------------------------------------+
|"$[1][0].k"                                |
+--------------------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','10',NULL,'$[1][0]');
+-----------------------------------------------+
| JSON_SEARCH(@j,'all','10',NULL,'$[1][0]') |
+-----------------------------------------------+
|"$[1][0].k"                                   |
+-----------------------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','abc',NULL,'$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j,'all','abc',NULL,'$[2]') |
+---------------------------------------------+
|"$[2].x"                                    |
+---------------------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','%a%');
+-------------------------------+
| JSON_SEARCH(@j,'all','%a%') |
+-------------------------------+
| ["$[0]","$[2].x"]            |
+-------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','%b%');
+-------------------------------+
| JSON_SEARCH(@j,'all','%b%') |
+-------------------------------+
| ["$[0]","$[2].x","$[3].y"]  |
+-------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','%b%',NULL,'$[0]');
+---------------------------------------------+
| JSON_SEARCH(@j,'all','%b%',NULL,'$[0]') |
+---------------------------------------------+
|"$[0]"                                      |
+---------------------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','%b%',NULL,'$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j,'all','%b%',NULL,'$[2]') |
+---------------------------------------------+
|"$[2].x"                                    |
+---------------------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','%b%',NULL,'$[1]');
+---------------------------------------------+
| JSON_SEARCH(@j,'all','%b%',NULL,'$[1]') |
+---------------------------------------------+
|NULL                                        |
+---------------------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','%b%','','$[1]');
+-------------------------------------------+
| JSON_SEARCH(@j,'all','%b%','','$[1]') |
+-------------------------------------------+
|NULL                                      |
+-------------------------------------------+
 
mysql>SELECT JSON_SEARCH(@j,'all','%b%','','$[3]');
+-------------------------------------------+
| JSON_SEARCH(@j,'all','%b%','','$[3]') |
+-------------------------------------------+
|"$[3].y"                                  |
+-------------------------------------------+

3.3 修改函数

3.3.1 JSON_INSERT函数

JSON_INSERT():插入值(往json中插入新值,但不替换已经存在的旧值)

  • 在指定path下插入数据,如果path已存在,则忽略此val(不存在才插入)。
  • 用法:JSON_INSERT(json_doc, path, val[, path, val] …)

例1:

mysql>SET @j ='{ "a": 1, "b": [2, 3]}';
mysql>SELECT JSON_INSERT(@j,'$.a', 10,'$.c','[true, false]');
+----------------------------------------------------+
| JSON_INSERT(@j,'$.a', 10,'$.c','[true, false]') |
+----------------------------------------------------+
| {"a": 1,"b": [2, 3],"c":"[true, false]"}        |
+----------------------------------------------------+

例2:

UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部门2','$.newData2','新增的数据2') 
WHERE id=2

image-1657430058645

可以看到由于json_doc将之前的值覆盖了,新增了deptName和newData2。
如果我们再执行一下刚才的那个sql,只是换了value,我们会看到里面的key值不会发生变化。因为这个函数只负责往json中插入新值,但不替换已经存在的旧值。

3.3.2 JSON_ARRAY_INSERT函数

在path指定的json array元素插入val,原位置及以右的元素顺次右移。如果path指定的数据非json array元素,则略过此val;如果指定的元素下标超过json array的长度,则插入尾部。

  • JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)

例1:

mysql>SET @j ='["a", {"b": [1, 2]}, [3, 4]]';
mysql>SELECT JSON_ARRAY_INSERT(@j,'$[1]','x');
+------------------------------------+
| JSON_ARRAY_INSERT(@j,'$[1]','x') |
+------------------------------------+
| ["a","x", {"b": [1, 2]}, [3, 4]]  |
+------------------------------------+
mysql>SELECT JSON_ARRAY_INSERT(@j,'$[100]','x');
+--------------------------------------+
| JSON_ARRAY_INSERT(@j,'$[100]','x') |
+--------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4],"x"]    |
+--------------------------------------+
mysql>SELECT JSON_ARRAY_INSERT(@j,'$[1].b[0]','x');
+-----------------------------------------+
| JSON_ARRAY_INSERT(@j,'$[1].b[0]','x') |
+-----------------------------------------+
| ["a", {"b": ["x", 1, 2]}, [3, 4]]       |
+-----------------------------------------+
mysql>SELECT JSON_ARRAY_INSERT(@j,'$[2][1]','y');
+---------------------------------------+
| JSON_ARRAY_INSERT(@j,'$[2][1]','y') |
+---------------------------------------+
| ["a", {"b": [1, 2]}, [3,"y", 4]]     |
+---------------------------------------+
mysql>SELECT JSON_ARRAY_INSERT(@j,'$[0]','x','$[2][1]','y');
+----------------------------------------------------+
| JSON_ARRAY_INSERT(@j,'$[0]','x','$[2][1]','y') |
+----------------------------------------------------+
| ["x","a", {"b": [1, 2]}, [3, 4]]                  |
+----------------------------------------------------+

3.3.3 JSON_REPLACE函数

JSON_REPLACE():更新函数,替换json中某个属性的值

  • 替换指定路径的数据,如果某个路径不存在则略过(存在才替换)。如果有参数为NULL,则返回NULL。
  • 用法:JSON_REPLACE(json_doc, path, val[, path, val] …)

例1:

mysql>SET @j ='{ "a": 1, "b": [2, 3]}';
mysql>SELECT JSON_REPLACE(@j,'$.a', 10,'$.c','[true, false]');
+-----------------------------------------------------+
| JSON_REPLACE(@j,'$.a', 10,'$.c','[true, false]') |
+-----------------------------------------------------+
| {"a": 10,"b": [2, 3]}                              |
+-----------------------------------------------------+

例2: 如我们要更新id=2数据中newData2的值为:更新的数据2

UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}', '$.newData2', '更新的数据2') WHERE id =2;

select * from dept WHERE id =2

image-1657498544362

3.3.4 JSON_SET函数

JSON_SET():将数据插入JSON格式中,有key则替换,无key则新增。是使用频率很高的一个函数。

  • 设置指定路径的数据(不管是否存在)。如果有参数为NULL,则返回NULL。
  • 用法:JSON_SET(json_doc, path, val[, path, val] …)

例1:

mysql>SET @j ='{ "a": 1, "b": [2, 3]}';
mysql>SELECT JSON_SET(@j,'$.a', 10,'$.c','[true, false]');
+-------------------------------------------------+
| JSON_SET(@j,'$.a', 10,'$.c','[true, false]') |
+-------------------------------------------------+
| {"a": 10,"b": [2, 3],"c":"[true, false]"}    |
+-------------------------------------------------+
mysql>SELECT JSON_INSERT(@j,'$.a', 10,'$.c','[true, false]');
+----------------------------------------------------+
| JSON_INSERT(@j,'$.a', 10,'$.c','[true, false]') |
+----------------------------------------------------+
| {"a": 1,"b": [2, 3],"c":"[true, false]"}        |
+----------------------------------------------------+
mysql>SELECT JSON_REPLACE(@j,'$.a', 10,'$.c','[true, false]');
+-----------------------------------------------------+
| JSON_REPLACE(@j,'$.a', 10,'$.c','[true, false]') |
+-----------------------------------------------------+
| {"a": 10,"b": [2, 3]}                              |
+-----------------------------------------------------+

例2: 如我们想针对id=2的数据新增一组:newData:新增的数据,修改deptName为新增的部门1

update dept set json_value=JSON_SET('{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2;

select * from dept WHERE id =2

image-1657429879411

注意:json_doc如果不带这个单元格之前的值,之前的值是会新值被覆盖的,比如我们如果更新的语句换成:

update dept set json_value=JSON_SET('{"a":"1","b":"2"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2

我们可以看到这里json_doc是{“a”:“1”,“b”:“2”},这样的话会把之前的单元格值覆盖后再新增/覆盖这个单元格字段

image-1657429986372

3.3.5 JSON_APPEND/JSON_ARRAY_APPEND函数

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

  • json_append 已废弃,从MySQL 5.7.9开始改名为json_array_append
  • 用法:JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)

例1:

mysql>SET @j ='["a", ["b", "c"], "d"]';
mysql>SELECT JSON_ARRAY_APPEND(@j,'$[1]', 1);
+----------------------------------+
| JSON_ARRAY_APPEND(@j,'$[1]', 1) |
+----------------------------------+
| ["a", ["b","c", 1],"d"]        |
+----------------------------------+
mysql>SELECT JSON_ARRAY_APPEND(@j,'$[0]', 2);
+----------------------------------+
| JSON_ARRAY_APPEND(@j,'$[0]', 2) |
+----------------------------------+
| [["a", 2], ["b","c"],"d"]      |
+----------------------------------+
mysql>SELECT JSON_ARRAY_APPEND(@j,'$[1][0]', 3);
+-------------------------------------+
| JSON_ARRAY_APPEND(@j,'$[1][0]', 3) |
+-------------------------------------+
| ["a", [["b", 3],"c"],"d"]         |
+-------------------------------------+
 
mysql>SET @j ='{"a": 1, "b": [2, 3], "c": 4}';
mysql>SELECT JSON_ARRAY_APPEND(@j,'$.b','x');
+------------------------------------+
| JSON_ARRAY_APPEND(@j,'$.b','x')  |
+------------------------------------+
| {"a": 1,"b": [2, 3,"x"],"c": 4} |
+------------------------------------+
mysql>SELECT JSON_ARRAY_APPEND(@j,'$.c','y');
+--------------------------------------+
| JSON_ARRAY_APPEND(@j,'$.c','y')    |
+--------------------------------------+
| {"a": 1,"b": [2, 3],"c": [4,"y"]} |
+--------------------------------------+
 
mysql>SET @j ='{"a": 1}';
mysql>SELECT JSON_ARRAY_APPEND(@j,'$','z');
+---------------------------------+
| JSON_ARRAY_APPEND(@j,'$','z') |
+---------------------------------+
| [{"a": 1},"z"]                 |
+---------------------------------+

3.3.6 JSON_MERGE函数

merge多个json文档。规则如下:

  • 如果都是json array,则结果自动merge为一个json array;
  • 如果都是json object,则结果自动merge为一个json object;
  • 如果有多种类型,则将非json array的元素封装成json array再按照规则一进行mege。
  • 用法:JSON_MERGE(json_doc, json_doc[, json_doc] …)

例1:

mysql>SELECT JSON_MERGE('[1, 2]','[true, false]');
+---------------------------------------+
| JSON_MERGE('[1, 2]','[true, false]') |
+---------------------------------------+
| [1, 2,true,false]                   |
+---------------------------------------+
mysql>SELECT JSON_MERGE('{"name": "x"}','{"id": 47}');
+-------------------------------------------+
| JSON_MERGE('{"name": "x"}','{"id": 47}') |
+-------------------------------------------+
| {"id": 47,"name":"x"}                   |
+-------------------------------------------+
mysql>SELECT JSON_MERGE('1','true');
+-------------------------+
| JSON_MERGE('1','true') |
+-------------------------+
| [1,true]               |
+-------------------------+
mysql>SELECT JSON_MERGE('[1, 2]','{"id": 47}');
+------------------------------------+
| JSON_MERGE('[1, 2]','{"id": 47}') |
+------------------------------------+
| [1, 2, {"id": 47}]                 |
+------------------------------------+

3.3.7 JSON_REMOVE函数

JSON_REMOVE():从JSON文档中删除数据

  • 移除指定路径的数据,如果某个路径不存在则略过此路径。如果有参数为NULL,则返回NULL。
  • 用法:JSON_REMOVE(json_doc, path[, path] …)

例1:

mysql>SET @j ='["a", ["b", "c"], "d"]';
mysql>SELECT JSON_REMOVE(@j,'$[1]');
+-------------------------+
| JSON_REMOVE(@j,'$[1]') |
+-------------------------+
| ["a","d"]              |
+-------------------------+

例2: 删除key为a的字段。

UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "更新的数据2"}','$.a') WHERE id =2;

3.3.8 JSON_UNQUOTE函数

JSON_UNQUOTE():去掉val的引号

  • 去掉val的引号。如果val为NULL,则返回NULL。
  • 用法:JSON_UNQUOTE(val)

例1:

mysql>SET @j ='"abc"';
mysql>SELECT @j, JSON_UNQUOTE(@j);
+-------+------------------+
| @j    | JSON_UNQUOTE(@j) |
+-------+------------------+
|"abc" | abc              |
+-------+------------------+
mysql>SET @j ='[1, 2, 3]';
mysql>SELECT @j, JSON_UNQUOTE(@j);
+-----------+------------------+
| @j        | JSON_UNQUOTE(@j) |
+-----------+------------------+
| [1, 2, 3] | [1, 2, 3]        |
+-----------+------------------+

3.4 JSON特性查询

3.4.1 JSON_TYPE函数

JSON_TYPE():查询某个json字段属性类型

  • 获取json文档的具体类型。如果参数为NULL,则返回NULL。
  • 用法:JSON_TYPE(json_val)

例1: 如我们想查询deptName的字段属性是什么:

SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept 

image-1657427819225

3.4.2 JSON_VALID函数

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

  • 用法:JSON_VALID(val)

例1:

mysql>SELECT JSON_VALID('{"a": 1}');
+------------------------+
| JSON_VALID('{"a": 1}') |
+------------------------+
|                      1 |
+------------------------+
mysql>SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
+---------------------+-----------------------+
| JSON_VALID('hello') | JSON_VALID('"hello"') |
+---------------------+-----------------------+
|                   0 |                     1 |
+---------------------+-----------------------+

3.4.3 JSON_LENGTH函数

JSON_LENGTH():获取指定路径下的长度。如果参数为NULL,则返回NULL。

  • 长度的计算规则:
    • 标量的长度为1;
    • json array的长度为元素的个数;
    • json object的长度为key的个数。
  • 用法:JSON_LENGTH(json_doc[, path])

例1:

mysql>SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
+---------------------------------+
| JSON_LENGTH('[1, 2, {"a": 3}]') |
+---------------------------------+
|                               3 |
+---------------------------------+
mysql>SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
|                                       2 |
+-----------------------------------------+
mysql>SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}','$.b');
+------------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}','$.b') |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

3.4.4 JSON_DEEPTH函数

JSON_DEEPTH():获取json文档的深度。如果参数为NULL,则返回NULL。

  • 空的json array、json object或标量的深度为1。
  • 用法:JSON_DEPTH(json_doc)
mysql>SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
+------------------+------------------+--------------------+
| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
+------------------+------------------+--------------------+
|                1 |                1 |                  1 |
+------------------+------------------+--------------------+
mysql>SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
+------------------------+------------------------+
| JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') |
+------------------------+------------------------+
|                      2 |                      2 |
+------------------------+------------------------+
mysql>SELECT JSON_DEPTH('[10, {"a": 20}]');
+-------------------------------+
| JSON_DEPTH('[10, {"a": 20}]') |
+-------------------------------+
|                             3 |
+-------------------------------+

4、几个函数的区别

4.1 JSON_REPLACE与JSON_SET的区别

// 旧值存在
mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere');
+----------------------------------------------------------------+
| JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |
+----------------------------------------------------------------+
| {"id": 29, "name": "Mere"}                                     |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', "Mere");
+------------------------------------------------------------+
| JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |
+------------------------------------------------------------+
| {"id": 29, "name": "Mere"}                                 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

// 旧值不存在
mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');
+---------------------------------------------------------------+
| JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |
+---------------------------------------------------------------+
| {"id": 29, "name": "Taylor"}                                  |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');
+-----------------------------------------------------------+
| JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |
+-----------------------------------------------------------+
| {"id": 29, "cat": "Mere", "name": "Taylor"}               |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

4.2 JSON_INSERT和JSON_SET的区别

// 旧值存在
mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[1]', 4);
+-------------------------------------+
| JSON_INSERT('[1, 2, 3]', '$[1]', 4) |
+-------------------------------------+
| [1, 2, 3]                           |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SET('[1, 2, 3]', '$[1]', 4);
+----------------------------------+
| JSON_SET('[1, 2, 3]', '$[1]', 4) |
+----------------------------------+
| [1, 4, 3]                        |
+----------------------------------+
1 row in set (0.00 sec)

//旧值不存在
mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[4]', 4);
+-------------------------------------+
| JSON_INSERT('[1, 2, 3]', '$[4]', 4) |
+-------------------------------------+
| [1, 2, 3, 4]                        |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SET('[1, 2, 3]', '$[4]', 4);
+----------------------------------+
| JSON_SET('[1, 2, 3]', '$[4]', 4) |
+----------------------------------+
| [1, 2, 3, 4]                     |
+----------------------------------+
1 row in set (0.00 sec)

5、索引的使用

JSON 字段不支持原生索引,需要基于 JSON 字段创建一个生成列,然后给这个生成列创建索引。插入数据时会自动填充生成列。
使用生成列作为条件,可以执行 like 模糊搜索,索引是生效的。

新建一个表

CREATE TABLE `t_json_tbl2` ( 
  `id` bigint(20) NOT NULL AUTO_INCREMENT,  
  `json_obj` json DEFAULT NULL COMMENT 'json 数据',  
  `gen_col` int(11) GENERATED ALWAYS AS (json_extract(`json_obj`,'$.num')) VIRTUAL COMMENT '生成列',  
  PRIMARY KEY (`id`),  
  KEY `idx_gen_col` (`gen_col`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

写个存储过程,插入大量数据

drop PROCEDURE if exists batchInsert;
DELIMITER $$
create PROCEDURE batchInsert(n int)
begin
declare i int default 0;
SET autocommit=0;
while i <= n
do
set i = i+1;
insert into t_json_tbl2(json_obj) values(JSON_OBJECT('name', concat('lisi-', i), 'num', i));
end while;
SET autocommit=1;
end $$
DELIMITER ;
 
-- 调用
call batchInsert(5000000);

查询

-- 可以这么写
select * from t_json_tbl2 where gen_col = 555555;
-- 或者这么写
select * from t_json_tbl2 where json_obj->'$.num' = 555555;

查看执行计划

数值型的使用->可以生效,但是字符串型的需要用生成列作为索引条件才能生效

explain select * from t_json_tbl2 where gen_col = 555555;

image-1657502685776

explain select * from t_json_tbl2 where json_obj->'$.num' = 555555;

image-1657502723394

6、Java 操作示例

使用 Mybatis-plus 操作,需要配置类型处理器

实体类

@Data
@TableName(value = "t_json_tbl", autoResultMap=true)
public class JsonTbl implements Serializable {
    private static final long serialVersionUID = 1L;
 
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
 
    /**
     * json 对象字段
     * 给 mybatis-plus 配置类型处理器
     */
    @TableField(typeHandler = JacksonTypeHandler.class)
    private JsonObj jsonObj;
 
    /**
     * json 数组字段
     */
    private String jsonArr;
 
    /**
     * json 格式字符串字段
     */
    private String jsonStr;
 
}
@Data
public class JsonObj {
    private String name;
    private Integer age;
}

mapper

public interface JsonTblMapper extends BaseMapper<JsonTbl> {
 
    JsonTbl selectBy(Long id);
    JsonTbl selectLike(String name);
 
}

xml 配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="top.originyy.case11.mybatis.mapper.JsonTblMapper">
 
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="top.originyy.case11.mybatis.entity.JsonTbl">
        <id column="id" property="id"/>
        <!--    配置类型处理器    -->
        <result column="json_obj" property="jsonObj"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
        <result column="json_arr" property="jsonArr"/>
        <result column="json_str" property="jsonStr"/>
    </resultMap>
 
    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, json_obj, json_arr, json_str
    </sql>
    
    <!--  自定义 sql 使用 resultMap 返回数据 -->
    <select id="selectBy" resultMap="BaseResultMap">
        select *
        from t_json_tbl
        where
        `id`=#{id}
    </select>
 
    <!--  自定义 sql 使用 resultMap 返回数据 -->
    <select id="selectLike" resultMap="BaseResultMap">
        select *
        from t_json_tbl
        where
        `json_obj`->'$.name'=#{name}
    </select>
 
</mapper>
0

评论区