mysql操作json字段实现数据过滤和查询

ゝ一世哀愁。 2023-10-04 22:32 26阅读 0赞

## 一.前置条件 ##

### 1.数据库表准备 ###

以下是数据库中的表和字段，其中包含了很多json字段表名为：t\_erecord\_device\_ext

![afeedbd2d2884f59a3f0e8516d8cd19a.png][]

其中produce\_info字段存储数据结构如下所示

![19d5bb03ecce4642a488c6dcebf86fef.png][]

实际数据如下所示

![88ab991af2684269be027de3ff5fc9ec.png][]

下面的需求都是需要基于这张表 操作json字段实现数据过滤和查询

## 二.mysql根据json数据过滤 ##

### 1.需求：筛选produce\_info字段中、brand不为空和不为null的数据 ###

sql实现如下：

SELECT
    	*
    FROM
    	view_device_info 
    WHERE
    	produce_info ->> '$.brand' != '' 
    	AND produce_info ->> '$.brand' != 'null'

查询结果如下：

![8939f49ef763463baa2135e57e9e7fde.png][]

## 三.mysql去除json字段brand查询出来的双引号，并且根据brand分组、查询brand同类型的device\_id并使用逗号拼接，过滤空值和null值 ##

sql实现如下：

SELECT
    	json_unquote(json_extract( produce_info, '$.brand' )) AS brand,
    	group_concat( device_id ) AS deviceIds 
    FROM
    	view_device_info 
    WHERE
    	STATUS = 0 
    	AND produce_info ->> '$.brand' != '' 
    	AND produce_info ->> '$.brand' != 'null' 
    	AND owner_id = 'AHHF_QHHFY_20180408' 
    GROUP BY
    	brand

查询结果如下：

![eb8eb68ea51c4905af8c9bbd14182028.png][]

### 四.mysql操作json数组的方式 ###

表中json字段如下

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>score_details&nbsp;</td> 
  </tr> 
  <tr> 
   <td>[{"score":60.0,"indicatorId":"1"},{"score":60.0,"indicatorId":"26"},{"score":60.0,"indicatorId":"9"},{"score":60.0,"indicatorId":"20"}]</td> 
  </tr> 
 </tbody> 
</table>

\# 如果score\_details 无数据,赋值为空数组

SELECT
    json_extract(if(LENGTH(score_details)>0,score_details, '[]'),"$[0]") one,
    json_extract(if(LENGTH(score_details)>0,score_details, '[]'),"$[1]") two ,
    json_extract(if(LENGTH(score_details)>0,score_details, '[]'),"$[2]") three,
    json_extract(if(LENGTH(score_details)>0,score_details, '[]'),"$[3]") four,
    json_extract(if(LENGTH(score_details)>0,score_details, '[]'),"$[4]") five
    FROM risk_assess_record where  id = '1096261165882802176lnesxwfewolu19'

查询结果如下

![f24e99271fce48a1935d81922451cf72.png][]

得到这个结果后再根据原先处理json的方式去拿数据即可

sql解释如下：

json_unquote：去除json中的双引号
    json_extract：解析json
    group_concat：合并聚合后的需要拼接的字段

[afeedbd2d2884f59a3f0e8516d8cd19a.png]: https://img-blog.csdnimg.cn/afeedbd2d2884f59a3f0e8516d8cd19a.png
[19d5bb03ecce4642a488c6dcebf86fef.png]: https://img-blog.csdnimg.cn/19d5bb03ecce4642a488c6dcebf86fef.png
[88ab991af2684269be027de3ff5fc9ec.png]: https://img-blog.csdnimg.cn/88ab991af2684269be027de3ff5fc9ec.png
[8939f49ef763463baa2135e57e9e7fde.png]: https://img-blog.csdnimg.cn/8939f49ef763463baa2135e57e9e7fde.png
[eb8eb68ea51c4905af8c9bbd14182028.png]: https://img-blog.csdnimg.cn/eb8eb68ea51c4905af8c9bbd14182028.png
[f24e99271fce48a1935d81922451cf72.png]: https://img-blog.csdnimg.cn/f24e99271fce48a1935d81922451cf72.png