广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MySQL对JSON类型字段数据进行提取和查询的实现
  • 552

0
分享到

MySQL对JSON类型字段数据进行提取和查询的实现

2024-04-02 19:04:59 552人浏览 八月长安
摘要

目录前言1. 问题现象2. 解决方案3. JSON数据查询3.1 一般基础查询操作3.2 一般函数查询操作4. jsON数据新增更新删除前言 昨天上线后通过系统报警发现了一

前言

昨天上线后通过系统报警发现了一个bug，于是紧急进行了回滚操作，但是期间有用户下单，数据产生了影响，因此需要排查影响了哪些订单，并对数据进行修复。

1. 问题现象

由于bug导致了订单表的customer_extra_info字段的hasFreightInsurance误更新成了“是”，因此需要查询回滚前一共有多少被误更新为“是”的订单，如下图：

于是查看订单表中customer_extra_info字段类型发现是JSON类型的

2. 解决方案

查询资料发现Mysql5.7以后提供了一种新的字段格式-json。

对JSON类型的数据mysql提供了相关的查询操作。

先给出查询sql，后面在介绍MySQL对JSON类型字段的查询操作

SELECT
	* 
FROM
	( SELECT id, customer_extra_info -> '$.hasFreightInsurance' AS Insurance FROM oms_order_list WHERE project_id = 1 AND update_time > '2022-04-15 16:30:17' ) t 
WHERE
	JSON_CONTaiNS ( Insurance, JSON_OBJECT ( "value", "是" ) )

3. JSON数据查询

3.1 一般基础查询操作

1、使用 json字段名->’$.json属性’ 进行查询条件

SELECT
	id,
	customer_extra_info
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'
	AND customer_extra_info -> '$.maxClaimAmount'=10

查询结果如下：

 2、关联表查询

json字段也支持关联表的查询，这里只写出使用方法，不做实例展示。其中deptLeaderId和id分别是dept,dept_leader两个表中的关联字段。

SELECT * from dept,dept_leader WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;

3.2 一般函数查询操作

写到这里大家都发现了，我们查询的json都是整条json数据，这样看起来不是很方便，那么如果我们只想看json中的某个字段怎么办？

这样就引入了我们的第一个函数:json_extract(字段名,json字段名)

在详细介绍用法之前我们可以看看官网的函数介绍：

在这里插入图片描述

 咱们可以看到官网介绍json_extract（）这个函数很详细：Return data from JSON document

从json中返回字段

1、函数 json_extract（）：从json中返回想要的字段

用法：json_extract(字段名,$.json字段名)
实例：

SELECT
	id,
	json_extract ( customer_extra_info, '$.hasFreightInsurance' ) AS hasFreightInsurance 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

查询结果如下：

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

用法: JSON_CONTAINS(target, candidate[, path])
实例：

SELECT
	id,
	customer_extra_info 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( customer_extra_info, JSON_OBJECT ( "maxClaimAmount", 10 ) )

查询结果如下：

3、函数JSON_OBJECT():将一个键值对列表转换成json对象 

比如我们想查询某个对象里面的值等于多少

我们可以看到hasFreightInsurance中还有一个对象，里面还有name和value两个属性字段，那么我们应该怎么查询value=否的订单呢。

用法：JSON_OBJECT([key, val[, key, val] …])
实例：

SELECT
	* 
FROM
	( SELECT id, customer_extra_info -> '$.hasFreightInsurance' AS Insurance FROM oms_order_list WHERE project_id = 1 AND update_time > '2022-04-15 16:30:17' ) t 
WHERE
	JSON_CONTAINS ( Insurance, JSON_OBJECT ( "value", "否" ) )

查询结果如下：

4、函数JSON_ARRAY():创建JSON数组

​用法：JSON_ARRAY([val[, val] …])

实例：我们要查询deptName包含1的数据

SELECT
	id,
	customer_extra_info 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( customer_extra_info -> '$.deptName', JSON_ARRAY ( "1" ) )

查询结果如下：

5、函数JSON_TYPE(）：查询某个json字段属性类型

用法：JSON_TYPE(json_val)
事例：比如我们想查询deptName的字段属性是什么

SELECT
	id,
	customer_extra_info -> '$.deptName',
	JSON_TYPE ( customer_extra_info -> '$.deptName' ),
	customer_extra_info -> '$.hasFreightInsurance',
	JSON_TYPE ( customer_extra_info -> '$.hasFreightInsurance' ) 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

查询结果如下：

6、函数JSON_EXTRACT() ：从JSON文档返回数据 

这也是我们开发中会经常用到的一个函数

SELECT
	* 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( JSON_EXTRACT ( customer_extra_info, '$.hasFreightInsurance' ), JSON_OBJECT ( "value", "否" ) )

 查询结果如下：

7、函数JSON_KEYS() ：JSON文档中的键数组

 用法：JSON_KEYS(json_value)

实例：比如我们想查询json格式数据中的所有key

SELECT
	id,
	JSON_KEYS ( customer_extra_info ) 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

 查询结果如下：

4. JSON数据新增更新删除

接下来的3种函数都是新增数据类型的：
JSON_SET(json_doc, path, val[, path, val] …)
JSON_INSERT(json_doc, path, val[, path, val] …)
JSON_REPLACE(json_doc, path, val[, path, val] …)

1、函数JSON_SET() ：将数据插入JSON格式中，有key则替换，无key则新增
这也是我们开发过程中经常会用到的一个函数

用法：JSON_SET(json_doc, path, val[, path, val] …)

实例：比如我们想针对id=2的数据新增一组：newData:新增的数据,修改deptName为新增的部门1
sql语句如下：

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

结果：

在这里插入图片描述

注意：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”}，这样的话会把之前的单元格值覆盖后再新增/覆盖这个单元格字段

结果:

在这里插入图片描述

2、函数JSON_INSERT():插入值（往json中插入新值，但不替换已经存在的旧值）
用法：JSON_INSERT(json_doc, path, val[, path, val] …)

 实例：

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

结果：

在这里插入图片描述

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

3、函数JSON_REPLACE（）
用法：JSON_REPLACE(json_doc, path, val[, path, val] …)

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

sql语句如下：

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

结果：

在这里插入图片描述

4、函数JSON_REMOVE() ：从JSON文档中删除数据
用法：JSON_REMOVE(json_doc, path[, path] …)

举例：删除key为a的字段。

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

结果：

在这里插入图片描述

5、函数JSON_SEARCH() ：用于在json格式中查询并返回符合条件的节点
这是一个非常强大的函数

到此这篇关于MySQL对JSON类型字段数据进行提取和查询的实现的文章就介绍到这了,更多相关MySQL JSON字段提取和查询内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: MySQL对JSON类型字段数据进行提取和查询的实现

本文链接: https://www.lsjlt.com/news/146790.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作