2

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?

GMB
  • 216,147
  • 25
  • 84
  • 135
JamLizzy101
  • 155
  • 1
  • 6

2 Answers2

3

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().

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks. That really helped. I wasn't getting any results, but I added json_unquote() to your query and it worked for me. I would appreciate your feedback – JamLizzy101 Jan 08 '20 at 15:15
  • 1
    @JamLizzy101: ah yes you are correct... We need `json_unquote()` to remove the quotes around the string that is returned by the JSON extrction. I edited my answer accordingly. – GMB Jan 08 '20 at 15:21
0

That seems to make more sense from the answer from GMB. Thanks a lot. I was still getting no records, but I managed to get it working from what GMB provided by adding json_unquote() from the following Stackoverflow post: Chaining JSON_EXTRACT with CAST or STR_TO_DATE fails

The SQL now looks like this: -

select 
user_id, 
json_unquote(user_profile->'$.gender') as gender, 
json_unquote(user_profile->'$.dateofbirth') as dateofbirth
from tipanel.user_profiles
where 
user_profile->"$.gender" = 'Female'
and str_to_date(json_unquote(user_profile->'$.dateofbirth'), "%d/%m/%Y") < '1988-06-15';

Comments are welcome if this can be done better.

JamLizzy101
  • 155
  • 1
  • 6