如何将JSON字符串时间转为可比较格式并筛选符合条件的数据？

我执行了这条SQL提取JSON数组里的start时间：

SELECT JSON_EXTRACT(z2schedule,'$[*].start') as startDate from cpmdev_z2weekly_schedule

得到的是JSON数组格式的结果，但想用下面的SQL把时间转成TIME格式并筛选大于指定时间的记录时，完全不生效：

SELECT JSON_EXTRACT(z2schedule,'$[*].start') as startDate 
from cpmdev_z2weekly_schedule 
where JSON_EXTRACT(z2schedule,CONVERT('$[*].start'),'TIME')>'CONVERT('2022-11-02 13:10:00:000', TIME)

我的需求很明确：把表中JSON数组里的start时间值提取出来，只保留大于指定时间（比如10:00:00）的结果。

举个例子，表中z2schedule字段存的是：

[{"start":"09:00:00.000","end":"17:00:00.000"},{"start":"10:00:00.000","end":"17:00:00.000"},{"start":"11:00:00.000","end":"17:00:00.000"}]

我要的结果是：

11:00:00.000

你的SQL问题出在两点：一是JSON_EXTRACT返回的是整个数组，没法直接转成TIME整体比较；二是转换函数的用法完全错误。

用JSON_TABLE（MySQL 8.0及以上版本支持）把JSON数组拆成单独的行，再处理时间筛选，正确SQL如下：

SELECT jt.start_time
FROM cpmdev_z2weekly_schedule
JOIN JSON_TABLE(
    z2schedule,
    '$[*]' COLUMNS (
        start_time TIME PATH '$.start'
    )
) AS jt
WHERE jt.start_time > '10:00:00';

简单解释 #

1. JSON_TABLE会把z2schedule里的JSON数组拆成多行，每行对应数组里的一个对象，同时直接把start字段转成TIME类型，命名为start_time
2. 用JOIN关联拆分后的结果，再通过WHERE条件筛选出时间大于10:00:00的记录
3. 如果你的MySQL版本低于8.0，建议升级到8.0版本使用这个方案，低版本拆分JSON数组的方法会非常繁琐

内容的提问来源于stack exchange，提问作者Gorakh Nath