MySql如何查询JSON字段值的指定key的数据

前言

昨天上线后通过系统报警发现了一个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格式中查询并返回符合条件的节点
这是一个非常强大的函数

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！