Querying records before a date from a JSON item's date in a JSON column in MySQL

Question

I am querying from a JSON field in the MySQL database which seems to work okay so far. It gives me records. But my issue is that I can't select where an item date in the JSON string is before a certain date. It seems to give me records before and after the given date, which it clearly doesn't seem to work. This is the code I have so far below: -

select 
user_id, 
json_extract(user_profile, "$.gender") as gender, 
json_extract(user_profile, "$.dateofbirth") as dateofbirth
from user_profiles 
where json_extract(user_profile, "$.gender") = "Female" 
and json_extract(user_profile, "$.dateofbirth") < "06/15/1988"

I considered using I considered using DATE_FORMAT() eg: -

where json_extract(user_profile, "$.gender") = "Female"
date_format(json_extract(user_profile, "$.dateofbirth"), "%d/%m/%Y") < "06/15/1988"

but then that just leaves me with no record. Is there any way to do this so MySQL can understand the date format from the JSON string that I am querying?

Answer 1

Assuming that dates in the json document are stored in dd/mm/yyyy format, then you want:

where 
    json_unquote(user_profile->"$.gender") = 'Female'
    and str_to_date(json_unquote(user_profile->"$.dateofbirth"), '%d/%m/%Y') < '1988-06-15'

Or:

where 
    json_unquote(user_profile->"$.gender") = 'Female'
    and str_to_date(user_profile->"$.dateofbirth", '"%d/%m/%Y"') < '1988-06-15'

str_to_date() converts the formated string to a date, that you can then compare with your fixed date.

Note: MySQL understands the -> notation, that can be used a shortcut for json_extract().