如何筛选JSON列中history键下时间戳在指定区间的行？

要筛选出history下存在时间戳键落在指定日期区间的行，最直接的方法是把history的键拆成单独的行，再逐一判断是否符合条件，具体可以用JSON_TABLE来实现：

示例SQL（直接用Unix时间戳区间） #

假设你要找Unix时间戳在1704067200（对应2024-01-01 00:00:00）到1706659199（对应2024-01-31 23:59:59）之间的行：

SELECT *
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        JSON_KEYS(value, '$.history'),
        '$[*]' COLUMNS(ts_str VARCHAR(20) PATH '$')
    ) AS history_ts
    WHERE CAST(ts_str AS UNSIGNED) BETWEEN 1704067200 AND 1706659199
);

用日期字符串直接筛选的版本 #

如果习惯用日期字符串而非Unix时间戳，可以结合FROM_UNIXTIME函数转换：

SELECT *
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        JSON_KEYS(value, '$.history'),
        '$[*]' COLUMNS(ts_str VARCHAR(20) PATH '$')
    ) AS history_ts
    WHERE FROM_UNIXTIME(CAST(ts_str AS UNSIGNED)) BETWEEN '2024-01-01' AND '2024-01-31 23:59:59'
);

关键逻辑说明 #

1. JSON_KEYS(value, '$.history')提取history下所有时间戳键，返回一个字符串数组
2. JSON_TABLE把数组拆成一行行的字符串值（ts_str），方便逐个校验
3. 将ts_str转为无符号整数后，即可和目标时间区间做对比
4. EXISTS子查询只要找到一个符合条件的时间戳，就会保留该行

注意事项 #

• 确保history的键都是有效的数字字符串，否则转换整数时会报错
• 该方法要求MySQL版本≥8.0，低版本不支持JSON_TABLE，需用自定义函数或其他方式遍历JSON键，复杂度会更高

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