MySQL对JSON类型字段数据进行提取和查询的实现
作者:慕城南风 发布时间:2024-01-23 02:48:54
前言
昨天上线后通过系统报警发现了一个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格式中查询并返回符合条件的节点
这是一个非常强大的函数
来源:https://blog.csdn.net/lovedingd/article/details/124300450


猜你喜欢
- 以前从来没有写过特别多的代码,这次在阅读论文的时候跑别人的代码的时候出现了很多import的问题,这里我想跟大家分享一下,我在Ubuntu系
- 前言上篇文章,讲了经典卷积神经网络-resnet,这篇文章通过resnet网络,做一些具体的事情。一、技术介绍总的来说,第一步首先要加载数据
- 本文记录了mysql 5.7.21 安装配置方法,分享给大家。1.下载安装包下面是官网windows系统的mysql下载地址Mysql下载地
- PyQt5信号与槽高级自定义信号与槽所谓高级自定义信号与槽,指的就是我们可以以自己喜欢的方式定义信号与槽函数,并传递参数,自定义信号的一般流
- 鼠标回调函数:def setMouseCallback(windowName, #窗口名称onMouse, &n
- 但是,当一本书学过之后,对一般的技术和函数都有了印象,突然想要查找某个函数的实例代码时,却感到很困难,因为一本书的源代码目录很长,往往有几十
- 一、回顾一下CONVERT()的语法格式:CONVERT (<data_ type>[ length ], <expres
- 操作命令:show binlog events in 'mysql-bin.000016' limit 10;reset m
- 返回pattern字符串在表达式expression里第一次出现的位置,起始值从1开始算。pattern字符串在expression表达式里
- 最近做接口对接,遇到了.net开发的webservice接口,因为python第一次与webservice对接,连问带查,最后使用suds库
- 前言由于两个表同一字段类型不一样,甚至是编码类型不一样也会导致查询不走索引,速度会很慢。强转直接举例说明:A 表id是int 类型 &nbs
- 一、name_scopewith tf.name_scope(name):name_scope: 为了更好地管理变量的命名空间而提出的。比如
- 介绍本文主要介绍如何在Flask框架中使用pyecharts,关于Flask框架使用这里不做具体说明~Flask模板渲染首先需要创建一个fl
- 神经网络梯度下降法在详细了解梯度下降的算法之前,我们先看看相关的一些概念。1. 步长(Learning rate):步长决定了在梯度下降迭代
- 有的小伙伴在学习数据库的时候,创建表结构的时候不小心把某字段设置成了varchar但是在统计求和的时候就傻眼了,接下来跟着小编学习一下,不用
- 宝塔更新Python版本因为在宝塔中的Python版本为2.6.8,使用宝塔Python项目管理的话需要把Python升级到3.x,不然的话
- OpenCV 是一个C++库,目前流行的计算机视觉编程库,用于实时处理计算机视觉方面的问题,它涵盖了很多计算机视觉领域的模块。在P
- 在前面实现了平移和缩放,还有一种常用的坐标变换,那就是旋转。比如拍摄的照片上传到电脑里,再打开时发现人的头在下面脚在上,这样肯定看不了,那么
- 一、修改 sonar 配置 conf/sonar.properties修改 sonar 配置文件 conf/sonar.properties
- 推荐两文:1、支付宝购买流程 2、支付宝卖家流程 支付宝接口提供最新的ASP、ASP.NET、PHP、JSP等目前网络上最流行的源码包文件,