MYSQL 解析json数据，并且查询json数组筛选符合条件的日期

我想实现的功能：param1存储的是json数据，格式为：[{"pass":"true","comment":"[通过]","complete":"600065","completeDate":"2022-04-19 09:07:58"},{"pass":"true","comment":"[通过]","complete":"600762","completeDate":"2022-04-22 09:08:40"},{"pass":"true","comment":"[通过]","complete":"600834","completeDate":"2022-04-22 09:09:20"}]

有的json长度 只有1 ，有的有几条。我想把  json中每个complete是我指定的。并且pass是true，并且completeDate日期我会进行范围查询的。也就是比如 pass=true，complete=600065，completeDate

> '2022-04-19 00:00:00' and  completeDate < '2022-04-19 23:59:59'

这样就筛选出来了这条数据

可能有的业务需求只要查询日期即可，更加简单，不用像我一样在sql中 concat。

实现效果：

1. 原始数据：只用两条id做展示

2. 给大家看下两个param1的数据

第一个数组长度为1
[{"pass":"true","comment":"1","complete":"cjy","completeDate":"2022-10-27 23:34:43"}]



第二个有好3条
[{"pass":"true","comment":"[通过]","complete":"600065","completeDate":"2022-04-19 09:07:58"},{"pass":"true","comment":"[通过]","complete":"600762","completeDate":"2022-04-22 09:08:40"},{"pass":"true","comment":"[通过]","complete":"600834","completeDate":"2022-04-22 09:09:20"}]

3. 查询sql

select id,
param1
from workflow_task

LEFT join 
 mysql.help_topic ht 
ON ht.help_topic_id < JSON_LENGTH(workflow_task.param1)

 where id in ('5de44908227b4bbe85026542ac700001' , '7072d5ceb54a4dd0a2fc882626300001') 

AND 
CONCAT(JSON_UNQUOTE(JSON_EXTRACT(param1, concat("$[",ht.help_topic_id,"].pass"))),':',JSON_UNQUOTE(JSON_EXTRACT(param1,  concat("$[",ht.help_topic_id,"].complete"))),':',JSON_UNQUOTE(JSON_EXTRACT(param1,  concat("$[",ht.help_topic_id,"].completeDate")))) BETWEEN 'true:600762:2022-04-22 00:00:00' AND 'true:600762:2022-04-22 23:59:59' ;

4. 最终结果：查询出来了这一条数据

 

开始写出来的是这个样子：

 

首先几个函数：

JSON_EXTRACT 解析json格式字符串。

JSON_UNQUOTE  去除 json的引号。

$[0].字段：就是取json数组 第一个对象 的 某个字段。上图可以看到取出日期

$[*].字段：就是取json数组 所有对象 的 某个字段，它会自动拼接成 ["","",""] 这种无法做sql的判断。比如无法在 where中添加 $[*].completeDate > xxx  < xxx。因为变成 [] 格式了。所以只能查看别的办法。

想到 只使用 $[0].xxx  $[1].xxx $[2].xxx 进行一个个拼接。类似下面：

select id,
JSON_EXTRACT(param1, '$[0].completeDate'), 
JSON_UNQUOTE(JSON_EXTRACT(param1, '$[0].completeDate')), 
JSON_UNQUOTE(JSON_EXTRACT(param1, '$[*].completeDate'))
,JSON_UNQUOTE(JSON_EXTRACT(param1, '$[0].completeDate'))
,CONCAT(JSON_UNQUOTE(JSON_EXTRACT(param1, '$[0].pass')),':',JSON_UNQUOTE(JSON_EXTRACT(param1, '$[0].complete')),':',JSON_UNQUOTE(JSON_EXTRACT(param1, '$[0].completeDate')))

from workflow_task where id in ('5de44908227b4bbe85026542ac700001' , '7072d5ceb54a4dd0a2fc882626300001') 

AND ( 

CONCAT(JSON_UNQUOTE(JSON_EXTRACT(param1, '$[0].pass')),':',JSON_UNQUOTE(JSON_EXTRACT(param1, '$[0].complete')),':',JSON_UNQUOTE(JSON_EXTRACT(param1, '$[0].completeDate'))) BETWEEN 'true:600065:2022-04-19 00:00:00' AND 'true:600065:2022-04-23 23:59:59' 


OR CONCAT(JSON_UNQUOTE(JSON_EXTRACT(param1, '$[1].pass')),':',JSON_UNQUOTE(JSON_EXTRACT(param1, '$[1].complete')),':',JSON_UNQUOTE(JSON_EXTRACT(param1, '$[1].completeDate'))) BETWEEN 'true:600065:2022-04-19 00:00:00' AND 'true:600065:2022-04-19 23:59:59'


OR CONCAT(JSON_UNQUOTE(JSON_EXTRACT(param1, '$[2].pass')),':',JSON_UNQUOTE(JSON_EXTRACT(param1, '$[2].complete')),':',JSON_UNQUOTE(JSON_EXTRACT(param1, '$[2].completeDate'))) BETWEEN 'true:600065:2022-04-19 00:00:00' AND 'true:600065:2022-04-19 23:59:59'
);

但是过于繁琐，网上查找了下，可以使用：help_topic

最终实现了json数组日期的范围查询功能。

参考：

mysql的json解析：查看MySQL中的JSON数组是否包含其键包含特定日期的对象_互联网集市

help_topic参考：MySQL数据库JSON数组拆分_mysql 拆分json数组_IT北辰的博客-CSDN博客