程序员需了解的SQL之JSON函数查询与修改（七）

最新推荐文章于 2024-07-14 03:42:35 发布
最新推荐文章于 2024-07-14 03:42:35 发布
阅读量1.2k 收藏 1
点赞数
分类专栏： Mysql 文章标签： mysql java
本文为博主原创文章版权归作者和CSDN共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
本文链接：https://blog.csdn.net/javaee_gao/article/details/113924442
版权

文章目录

前言

MySQL从5.7.8以后引入了JSON数据类型，对于JSON文档的操作除了简单的读和写之外还有很多处理JSON的函数。

JSON函数可以从增删改查这些功能点来深入学习Mysql提供的JSON函数.JSON支持包括NUMBER、STRING、BOOLEAN、NULL、ARRAY、OBJECT共6种。

1.创建JSON函数

  • 创建JSON数组
mysql> select json_array(1,'json',true,null,now());
+-------------------------------------------------------+
| json_array(1,'json',true,null,now())                  |
+-------------------------------------------------------+
| [1, "json", true, null, "2021-02-21 22:19:57.000000"] |
+-------------------------------------------------------+
1 row in set (0.01 sec)
  • 创建JSON对象
mysql> select json_object('name','张三','age',24);
+---------------------------------------+
| json_object('name','张三','age',24)   |
+---------------------------------------+
| {"age": 24, "name": "张三"}           |
+---------------------------------------+
1 row in set (0.00 sec)

2. 查询JSON函数

2.1 JSON_CONAINS(source,target,path)

此函数可以判断target是否包含在source中，其中path参数可选，如果有参数为NULL或者path不存在则返回NULL，存在返回1否则返回0。
下面以简单例子演示此函数使用：

## 创建一个JSON数组对象
mysql> select json_array(1,2,'abc',true,null);
+---------------------------------+
| json_array(1,2,'abc',true,null) |
+---------------------------------+
| [1, 2, "abc", true, null]       |
+---------------------------------+
1 row in set (0.00 sec)

然后判断元素abc、true、null、520是否存在上述JSON对象中

  • 判断元素abc是否包含JSON对象中
mysql> select json_contains('[1, 2, "abc", true, null]','"abc"');
+----------------------------------------------------+
| json_contains('[1, 2, "abc", true, null]','"abc"') |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
1 row in set (0.00 sec)
  • 判断元素true是否包含JSON对象中
mysql> select json_contains('[1, 2, "abc", true, null]','true');
+---------------------------------------------------+
| json_contains('[1, 2, "abc", true, null]','true') |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+
1 row in set (0.00 sec)
  • 判断元素null是否包含在JSON对象中
mysql> select json_contains('[1, 2, "abc", true, null]','null');
+---------------------------------------------------+
| json_contains('[1, 2, "abc", true, null]','null') |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+
1 row in set (0.00 sec)
  • 判断元素520是否包含在此函数中
mysql> select json_contains('[1, 2, "abc", true, null]','520');
+--------------------------------------------------+
| json_contains('[1, 2, "abc", true, null]','520') |
+--------------------------------------------------+
|                                                0 |
+--------------------------------------------------+
1 row in set (0.00 sec)
  • 判断数组[1,2,‘abc’] 是否存在JSON
mysql> select json_contains('[1, 2, "abc", true, null]','[1,2,"abc"]');
+----------------------------------------------------------+
| json_contains('[1, 2, "abc", true, null]','[1,2,"abc"]') |
+----------------------------------------------------------+
|                                                        1 |
+----------------------------------------------------------+
1 row in set (0.00 sec)

上面几个案例中我们使用JSON_CONTAINS 没有使用到path 参数，下面我们将演示带path参数的函数使用。
通常path以$.key,如果key是一个对象obj，则path应该为$.obj.key。

  • 创建JSON对象并存储在Mysql变量中
mysql> select json_object('name','张三','age',23);
+---------------------------------------+
| json_object('name','张三','age',23)   |
+---------------------------------------+
| {"age": 23, "name": "张三"}           |
+---------------------------------------+
1 row in set (0.00 sec)
## 创建name1变量
mysql> set @name1='{"age": 23, "name": "张三"}';
Query OK, 0 rows affected (0.00 sec)
  • 判断路径为$.name 是否存在值张三，包含返回1不包含返回0。
mysql> select json_contains(@name1,'"张三"','$.name');
+-------------------------------------------+
| json_contains(@name1,'"张三"','$.name')   |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

2.2 JSON_CONAINS_PATH(source,one or all,path…)

此函数查询指定的多个path参数是否存在JSON文档中，其中第二个参数只能取ONE或者ALL，分别表示匹配一个就可以以及匹配所有。

## 匹配JSON对象包含指定路径name或者xx
mysql> select json_contains_path(@name1,'one','$.name','$.xx');
+--------------------------------------------------+
| json_contains_path(@name1,'one','$.name','$.xx') |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+
1 row in set (0.00 sec)
## 匹配JSON对象包含指定路径name和xx
mysql> select json_contains_path(@name1,'all','$.name','$.age');
+---------------------------------------------------+
| json_contains_path(@name1,'all','$.name','$.age') |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+
1 row in set (0.00 sec)

2.3 JSON_EXTRACT(source,path…)

在JSON数组中可以path可以$[index] 表示数组中第index个元素，下面提取第index个元素如下：

## 创建JSON数组
mysql> select json_array('1','2',json_array('3','4'));
+-----------------------------------------+
| json_array('1','2',json_array('3','4')) |
+-----------------------------------------+
| ["1", "2", ["3", "4"]]                  |
+-----------------------------------------+
1 row in set (0.00 sec)

抽取第一个和第三个值

mysql> select json_extract('["1", "2", ["3", "4"]]','$[0]','$[2]');
+------------------------------------------------------+
| json_extract('["1", "2", ["3", "4"]]','$[0]','$[2]') |
+------------------------------------------------------+
| ["1", ["3", "4"]]                                    |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL5.7.9之后有一个更简单的标号-> 用来简化JSON_EXRACT,其语法如下：

列名 -> path

## 创建包含JSON类型的表如下
mysql> create table json_demo(content json);
Query OK, 0 rows affected (0.01 sec)
## 插入数据
mysql> insert into json_demo values(@name1);
Query OK, 1 row affected (0.01 sec)
## 查询数据
mysql> select * from json_demo;
+-------------------------------+
| content                       |
+-------------------------------+
| {"age": 23, "name": "张三"}   |
+-------------------------------+
1 row in set (0.00 sec)

请看如下SQL语句：

mysql> select content,json_extract(content,'$.name') from json_demo where json_extract(content,'$.name')='张三';
+-------------------------------+--------------------------------+
| content                       | json_extract(content,'$.name') |
+-------------------------------+--------------------------------+
| {"age": 23, "name": "张三"}   | "张三"                         |
+-------------------------------+--------------------------------+
1 row in set (0.00 sec)

等价于如下SQL：

mysql> select content,content -> '$.name' from json_demo where content -> '$.name'='张三';
+-------------------------------+---------------------+
| content                       | content -> '$.name' |
+-------------------------------+---------------------+
| {"age": 23, "name": "张三"}   | "张三"              |
+-------------------------------+---------------------+
1 row in set (0.00 sec)

JSON中查询为字符串类型都会带双引号，如果去掉双引号可以使用JSON_UNQUOTE 函数 如下所示：

mysql> select content,json_unquote(content -> '$.name') from json_demo where content -> '$.name'='张三';
+-------------------------------+-----------------------------------+
| content                       | json_unquote(content -> '$.name') |
+-------------------------------+-----------------------------------+
| {"age": 23, "name": "张三"}   | 张三                              |
+-------------------------------+-----------------------------------+
1 row in set (0.01 sec)

2.4JSON_KEYS(source,path)

此函数返回在指定路径下所有的键，下面简单演示其使用：

mysql> select json_keys(@name1);
+-------------------+
| json_keys(@name1) |
+-------------------+
| ["age", "name"]   |
+-------------------+
1 row in set (0.00 sec)
mysql>

创建嵌套JSON对象如下所示：

mysql> select json_object('name','张三','age','23','account',json_object('accountId','123456','accountBank','招商银行'));
+------------------------------------------------------------------------------------------------------------------+
| json_object('name','张三','age','23','account',json_object('accountId','123456','accountBank','招商银行'))       |
+------------------------------------------------------------------------------------------------------------------+
| {"age": "23", "name": "张三", "account": {"accountId": "123456", "accountBank": "招商银行"}}                     |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查询嵌套函数key

mysql> select json_keys(@name2);
+----------------------------+
| json_keys(@name2)          |
+----------------------------+
| ["age", "name", "account"] |
+----------------------------+
1 row in set (0.00 sec)

查询指定路径下嵌套的key

mysql> select json_keys(@name2,'$.account');
+-------------------------------+
| json_keys(@name2,'$.account') |
+-------------------------------+
| ["accountId", "accountBank"]  |
+-------------------------------+
1 row in set (0.00 sec)

2.5 JSON_SEARCH(source,one or all ,search_str)

此函数表示在source中匹配一个或者所有复合search_str的元素key，其中search_str 可使用类似like中模糊匹配如下：

## 查询JSON至少一个内容是23的key
mysql> select json_search(@name2,'one','23');
+--------------------------------+
| json_search(@name2,'one','23') |
+--------------------------------+
| "$.age"                        |
+--------------------------------+
1 row in set (0.00 sec)
## 查询JSON至少一个内容是以张开头的key
mysql> select json_search(@name2,'one','张%');
+----------------------------------+
| json_search(@name2,'one','张%')  |
+----------------------------------+
| "$.name"                         |
+----------------------------------+
1 row in set (0.00 sec)
## 查询内容包含2的所有key对象
mysql> select json_search(@name2,'all','%2%');
+----------------------------------+
| json_search(@name2,'all','%2%')  |
+----------------------------------+
| ["$.age", "$.account.accountId"] |
+----------------------------------+
1 row in set (0.00 sec)
## 创建新的JSON对象并赋值
mysql> select json_array('1','2',true,null,'abc');
+-------------------------------------+
| json_array('1','2',true,null,'abc') |
+-------------------------------------+
| ["1", "2", true, null, "abc"]       |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> set @v1='["1", "2", true, null, "abc"]';
Query OK, 0 rows affected (0.00 sec)
## 查询JSON数组
mysql> select json_search(@v1,'one','1');
+----------------------------+
| json_search(@v1,'one','1') |
+----------------------------+
| "$[0]"                     |
+----------------------------+
1 row in set (0.00 sec)

3. 修改JSON函数

3.1 JSON_ARRAY_APPEND(source,path,val)

此函数在指定path的JSON 数组尾部加val。如果指定path是一个JSON对象则将其封装城一个新的JSON Array。

mysql> select json_array_append(@v1,'$[0]','haha');
+-----------------------------------------+
| json_array_append(@v1,'$[0]','haha')    |
+-----------------------------------------+
| [["1", "haha"], "2", true, null, "abc"] |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select json_array_append('[["1", "haha"], "2", true, null, "abc"]','$[0]','xixi');
+----------------------------------------------------------------------------+
| json_array_append('[["1", "haha"], "2", true, null, "abc"]','$[0]','xixi') |
+----------------------------------------------------------------------------+
| [["1", "haha", "xixi"], "2", true, null, "abc"]                            |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
## 设置值
mysql> set @v2='[["1", "haha", "xixi"], "2", true, null, "abc"]';
Query OK, 0 rows affected (0.00 sec)
## 在第一个元素数组中第二个位置上追加 ai
mysql> select json_array_append(@v2,'$[0][1]','ai');
+---------------------------------------------------------+
| json_array_append(@v2,'$[0][1]','ai')                   |
+---------------------------------------------------------+
| [["1", ["haha", "ai"], "xixi"], "2", true, null, "abc"] |
+---------------------------------------------------------+
1 row in set (0.00 sec)

3.2 JSON_ARRAY_INSERT(source,path,val)

此函数可以在指定path下插入元素val，原先位置的元素整体右移。如果插入的目标元素非JSON数组则不会插入val元素，如果插入的元素超出JSON对象的长度则在尾部进行追加。

mysql> select json_array_insert(@name2,'$[0]','1');
+----------------------------------------------------------------------------------------------------+
| json_array_insert(@name2,'$[0]','1')                                                               |
+----------------------------------------------------------------------------------------------------+
| {"age": "23", "name": "张三", "account": {"accountId": "123456", "accountBank": "招商银行"}}       |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

上面的JSON对象插入没有效果，这是因为JSON对象是一个JSON_OBJECT类型的元素会自动忽略插入的元素‘1’。
接下来我们使用JSON_ARRAY插入对象看一看效果。

mysql> select json_array_insert('["1", "2", ["3", "4"]]','$[0]','x');
+--------------------------------------------------------+
| json_array_insert('["1", "2", ["3", "4"]]','$[0]','x') |
+--------------------------------------------------------+
| ["x", "1", "2", ["3", "4"]]                            |
+--------------------------------------------------------+
1 row in set (0.00 sec)

3.3 JSON_REPLACE(source,path,val)

此函数可以替换指定路径上的数据，如果path路径不存在则忽略。

mysql> select json_replace(@v1,'$[0]','y');
+------------------------------+
| json_replace(@v1,'$[0]','y') |
+------------------------------+
| ["y", "1", "2", ["3", "4"]]  |
+------------------------------+
1 row in set (0.00 sec)


mysql> select json_replace(@name2,'$.age','24');
+----------------------------------------------------------------------------------------------------+
| json_replace(@name2,'$.age','24')                                                                  |
+----------------------------------------------------------------------------------------------------+
| {"age": "24", "name": "张三", "account": {"accountId": "123456", "accountBank": "招商银行"}}       |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.4 JSON_SET(source,path,val)

此函数可以设置指定路径的数据，与JSON_REPLACE功能类似，唯一区别就是当指定路径不存在，会在尾部进行添加

mysql> select json_replace(@name2,'$.age11','24');
+----------------------------------------------------------------------------------------------------+
| json_replace(@name2,'$.age11','24')                                                                |
+----------------------------------------------------------------------------------------------------+
| {"age": "23", "name": "张三", "account": {"accountId": "123456", "accountBank": "招商银行"}}       |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我们发现此函数并不存在，我们在使用JSON_SET函数在看次效果如下：

mysql> select json_set(@name2,'$.age11','24');
+-------------------------------------------------------------------------------------------------------------------+
| json_set(@name2,'$.age11','24')                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| {"age": "23", "name": "张三", "age11": "24", "account": {"accountId": "123456", "accountBank": "招商银行"}}       |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.5 JSON_MERGE_PRESERVE(source1,source2)

此函数是可以合并两个JSON函数，合成规则如下：

  • 如果两者都是JSON数组，则合成一个JSON数组对象。
  • 如果都是JSON对象则合成一个JSON对象。
  • 如果是不同类型，则将非JSON数组对象转换成JSON数组在按照第一个规则进行合并

合并数组

mysql> select json_merge_preserve(@v1,json_array('a','b','c'));
+--------------------------------------------------+
| json_merge_preserve(@v1,json_array('a','b','c')) |
+--------------------------------------------------+
| ["x", "1", "2", ["3", "4"], "a", "b", "c"]       |
+--------------------------------------------------+
1 row in set (0.00 sec)

合并对象

mysql> select json_merge_preserve(@name2,json_object('company','神码'));
+-------------------------------------------------------------------------------------------------------------------------+
| json_merge_preserve(@name2,json_object('company','神码'))                                                               |
+-------------------------------------------------------------------------------------------------------------------------+
| {"age": "23", "name": "张三", "account": {"accountId": "123456", "accountBank": "招商银行"}, "company": "神码"}         |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

合并多类型

mysql> select json_merge_preserve(@v1,@name2);
+---------------------------------------------------------------------------------------------------------------------------------+
| json_merge_preserve(@v1,@name2)                                                                                                 |
+---------------------------------------------------------------------------------------------------------------------------------+
| ["x", "1", "2", ["3", "4"], {"age": "23", "name": "张三", "account": {"accountId": "123456", "accountBank": "招商银行"}}]       |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.5 JSON_REMOVE(source,path…)

此函数可以移除指定路径的数据，如果某个路径不存在则略过此路径。

mysql> select json_remove(@v1,'$[0]','$[1]');
+--------------------------------+
| json_remove(@v1,'$[0]','$[1]') |
+--------------------------------+
| ["1", ["3", "4"]]              |
+--------------------------------+
1 row in set (0.00 sec)

这里需要注意的是删除是串行操作的，即先删除$[0]后的JSON文档上基础继续删除$[1]上的元素。

Galen-gao
关注
专栏目录
JsonSQL:用SQL语句解析JSON文件
06-19
用SQL语法，当然只能用简单的条件查询咯，不过这个用起比较简单，容易上手
使用TSQL查询和更新 JSON 数据
weixin_34366546的博客
09-24 165
JSON是一个非常流行的，用于数据交换的文本数据（textual data）格式，主要用于Web和移动应用程序中。JSON 使用“键/值对”（Key:Value pair）存储数据，能够表示嵌套键值对和数组两种复杂数据类型，JSON仅仅使用逗号（引用Key）和中括号（引用数组元素），就能路由到指定的属性或成员，使用简单，功能强大。在SQL Server...
【面试数据岗必知必会——sql中的行转列、列转行与json函数】
frank-liang的博客
07-10 1258
【面试数据岗必知必会——sql中的行转列、列转行与json函数】
sql中几个常用的json解析函数
weixin_45052608的博客
11-09 2378
data：代表json格式的字段名 name：代表json中的元素 普通解析 mysql/ADB MYSQL json_extract(data,’$.name’) clickhouse data[‘name’] 特殊字符 mysql json_extract(data,’$.“name”’) ADB MYSQL json_array_get(data,索引) 索引为int型，从0开始，也就是直接解析array中的第几个元素 解析json元素中的列表 mysql json_extract(
SQL操作json类型数据的函数
weixin_33711641的博客
03-15 1744
MySQL5.7之后对json数据格式进行了函数支持 Json_contains(字段名, '值') 　　1、表中json格式的某个字段 　　2、json里的某个键值 Json_search(字段名, 'one', 'data%') 　　1、要查找的文档 　　2、查找的范围，有两个选项，‘one’查找第一个符合条件的，‘all’查找所有符合条件的 　　3、查找的条件 Json Pa...
如何写SQL对JSON数据进行查询
m0_55746612的博客
06-13 466
某一个字段存了一个对象，现在需要查询符合条件的对象出来，例如：查询一个中果的对象，并打印出她的种类。
作为 clickhouse 程序员，我想将 json array 转化为 int array，sql 应该如何写？
07-15
你可以使用ClickHouse内置的`JSONExtractArray`函数将JSON数组转换为整数数组。下面是一个示例SQL语句： ```sql SELECT arrayMap(x -> toInt32(x), JSONExtractArray('[1, 2, 3, 4]')) AS int_array; ``` 在这个...
程序员的SQL金典（杨中科）
11-09
2. 数据查询：详细阐述了SELECT语句的使用，包括基本的查询、聚合函数（如COUNT、SUM、AVG、MAX、MIN）、分组与排序（GROUP BY与ORDER BY）、子查询以及联接操作（INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN）等...
sqawk：与Awk类似，但具有SQL和表联接
01-30
《sqawk： Awk的增强版，融合SQL与表联接功能》 在信息技术领域，数据处理是一项核心任务，而工具的选择则直接影响到工作效率。Awk作为一种强大的文本分析工具，长期以来在程序员和系统管理员中备受青睐。然而，...
三级嵌套的json数据数据库查询sql
04-01
对于三级嵌套的JSON数据，可以使用PostgreSQL中的JSONB数据类型和相关函数来查询。 假设有以下JSON数据： ``` { "id": 1, "name": "Alice", "details": { "age": 25, "address": { "city": "New York", ...
MySQL5.7 JSON类型使用详解
09-09
4. JSON函数：MySQL为处理JSON类型数据提供了一系列函数，这些函数包括但不限于json_extract()、json_keys()、json_length()和json_replace()等。json_extract()函数可以提取JSON文档中的特定数据，json_keys()函数...
SQL Server之JSON 函数详解
09-09
本篇文件将结合MSND简要分析Sqlserver中JSON函数，主要包括ISJSON,JSON_VALUE,JSON_MODIFY,JSON_QUERY,需要的朋友可以参考下
MySQL_关于JSON数据的查询
m0_67390379的博客
07-29 1017
MySQL中关于JSON数据类型查询的方法当然不至这一种，有兴趣的可以具体去官网查看，MySQL官网-JSON函数参考以上是个人理解，如果有不对的地方，希望博友指出先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。httpshttps。...
mysql JSON查询sql json_search
最新发布
weixin_36952167的博客
07-14 61
如何实现mysql JSON查询sql json_search 介绍 在MySQL中，JSON数据类型的使用越来越广泛。通过使用json_search函数，可以在JSON数据中进行查询，以满足不同的需求。在本文中，我将指导您如何使用json_search函数进行JSON查询。 流程图 经验丰富的开发者小白经验丰富的开发者小...
浅谈axios回调函数this------- var that = this；
weixin_45655291的博客
01-18 889
先看一个例子： 在methods中，可以用this.city获取到data中的city数据，**但是在axios的then函数中，this回调值发生了变化，返回值为undefined **。所以在axios函数外面使用 var that = this；保存this指向。 var app = new Vue({ el:"#app", data:{ city:'武汉', weatherList:[], }, methods:{ se
MySQL 之 JSON 支持（三）—— JSON 函数
wzy0623的专栏
06-14 2026
JSON 函数官方文档翻译
SQL处理json数据
像豆芽一样优秀
07-20 8019
通过sql解析字符串、提取键值对数据。
Web前端工作笔记002---json数据查询的方法_json查询大全，JsonSQL数据查询，jfunk数据查询
添柴程序猿的专栏
09-23 2137
  JAVA技术交流QQ群：170933152   json数据查询的方法 网上看到有一篇帖子，有8种json数据查询的方法，大家可以研究一下，我现在分享一下！ JsonSQL JsonSQL实现了使用SQL select语句在json数据结构中查询的功能。 例子： $.getJSON("testjson.js", function(a){ console.dir(jsons...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值