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

一.前置条件

1.数据库表准备

以下是数据库中的表和字段，其中包含了很多json字段表名为：t_erecord_device_ext

 其中produce_info字段存储数据结构如下所示

实际数据如下所示

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

二.mysql根据json数据过滤

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

sql实现如下：

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

查询结果如下：

三.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

查询结果如下：

四.mysql操作json数组的方式

表中json字段如下

score_details

[{"score":60.0,"indicatorId":"1"},{"score":60.0,"indicatorId":"26"},{"score":60.0,"indicatorId":"9"},{"score":60.0,"indicatorId":"20"}]

# 如果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'  

查询结果如下

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

 sql解释如下：

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