I have a MySQL table called "contract" with a JSON-type column.
The data in this column looks like this:
{"5": "1663966800"}
This property is a UTC time in seconds, stored as text.
I am looking to query the table to see all entries where this property is greater than the current datetime.
What is the proper query for this?
I am trying to run the below and getting an "Invalid JSON path expression" error:
select `contract`.* from `contract`
where contract.id != 0
AND (contract.data->'$.5' > "1687893018")
You are very close. When using JSON data in MySQL, the -> operator can be used to extract a value from a JSON column. But since the value you're extracting is a string (not a number), you'll need to cast it as unsigned in order to perform the comparison.
SELECT `contract`.*
FROM `contract`
WHERE JSON_EXTRACT(`contract`.`data`, '$."5"') IS NOT NULL
AND CAST(JSON_EXTRACT(`contract`.`data`, '$."5"') AS UNSIGNED) > UNIX_TIMESTAMP(NOW());