MySQL中查询JSON字段内时间戳大于当前时间的正确语句？解决报错

问题描述 #

我有一个名为contract的MySQL表，其中包含一个JSON类型的列。该列的数据示例如下：

{"5": "1663966800"}

该属性值是存储为文本格式的UTC秒级时间戳。我希望查询表中该属性值大于当前时间的所有条目，但执行以下语句时出现了“Invalid JSON path expression”错误：

select `contract`.* from `contract` 
where contract.id != 0  
AND (contract.data->'$.5' > "1687893018")

正确查询语句及说明 #

1. 修复JSON路径语法错误 #

MySQL的JSON路径规则中，当键名为纯数字时，必须用双引号包裹键名，否则会触发路径表达式无效的错误。因此$.5需要修改为$."5"。

2. 文本时间戳转数值类型比较 #

JSON字段中的时间戳是文本格式，直接进行比较可能出现隐式转换问题，建议先去除引号并转换为整数类型，确保比较是基于数值的。可以使用->>运算符（等价于JSON_UNQUOTE(JSON_EXTRACT())）获取无引号的文本内容，再通过CAST转为无符号整数。

3. 动态获取当前时间戳 #

使用UNIX_TIMESTAMP()函数可以直接获取当前UTC秒级时间戳，无需手动输入固定值。

最终的正确查询语句如下：

SELECT `contract`.* 
FROM `contract` 
WHERE contract.id != 0  
AND CAST(contract.data->>'$."5"' AS UNSIGNED) > UNIX_TIMESTAMP();

也可以用JSON_EXTRACT结合JSON_UNQUOTE的写法：

SELECT `contract`.* 
FROM `contract` 
WHERE contract.id != 0  
AND CAST(JSON_UNQUOTE(JSON_EXTRACT(contract.data, '$."5"')) AS UNSIGNED) > UNIX_TIMESTAMP();

补充说明 #

如果你的MySQL版本是8.0及以上，还可以用JSON_VALUE函数简化写法：

SELECT `contract`.* 
FROM `contract` 
WHERE contract.id != 0  
AND CAST(JSON_VALUE(contract.data, '$."5"') AS UNSIGNED) > UNIX_TIMESTAMP();

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