0

Is there some way to compare hours when extracting from JSON type of data?

Currently I have this query: 

SELECT * FROM
(SELECT clinics.id as cid, clinics.lat, clinics.lng, clinics.opening_hours,
clinics.logo, clinics.address, clinics.city, clinics.state, clinics.name, clinics.description,
clinics.zip, clinics.phone_number, clinics.email, clinics.url, clinics.bookmark_url,
clinics.marker, timezone,
countries.full_name AS country,
(6378 * acos(
cos(radians(-33.8688197)) * cos(radians(lat)) *
cos(radians(lng) - radians(151.2092955)) +
sin(radians(-33.8688197)) * sin(radians(lat))))
AS distance
FROM clinics
JOIN countries ON countries.id = clinics.country_id
LEFT JOIN clinics_services ON clinics.id = clinics_services.clinic_id
WHERE CAST(JSON_EXTRACT(`opening_hours`, '$."wednesday-to"') as time) > CAST('09:00' as time)
GROUP BY clinics.id
) AS distances
WHERE distance < 5
ORDER BY distance ASC

If I run this query I am getting proper results:

SELECT JSON_EXTRACT(`opening_hours`, '$."wednesday-to"') FROM clinics

(results):

"17:30"
"19:00"
"12:00"
"19:00"
"19:00"
"19:00"
"19:00"
"19:00"
"19:00"
"19:00"
"19:00"
"19:00"
"19:00"
"19:00"
"19:00"
"19:00"
"19:00"
"18:00"
"19:00"
"18:00"
"18:00"
"18:00"
"18:00"
"18:00"
"19:00"

But if I try to cast it to time (with this query), I am getting NULL:

SELECT CAST(JSON_EXTRACT(`opening_hours`, '$."wednesday-to"') as TIME) FROM clinics

What am I doing wrong?

Ratish Bansal
  • 1,982
  • 1
  • 10
  • 19
Sasha
  • 8,521
  • 23
  • 91
  • 174
  • 2
    Use the `JSON_UNQUOTE(JSON_EXTRACT(...))` function to strip the literal double-quotes out of a value you have extracted. Or else just store your data in a normalized fashion, and forget about JSON. Virtually every time I've seen questions about JSON in MySQL, it would have been solved more easily by storing data in individual columns. – Bill Karwin Jan 29 '19 at 15:37
  • if you are sure about your data format, you can do just `JSON_EXTRACT(\`opening_hours\`, '$."wednesday-to"') > '09:00'` – Alex Jan 29 '19 at 15:52

1 Answers1

1

It seem @BillKarwin is right. You need to wrap JSON_EXTRACT with JSON_UNIQUOTE.

Chaining JSON_EXTRACT with CAST or STR_TO_DATE fails

https://www.db-fiddle.com/f/nomb47TQ2Rxaw5MGc1WtSs/0

SELECT CAST(JSON_UNQUOTE(JSON_EXTRACT('{"hour":"10:00"}','$.hour')) as time) > CAST('09:00' as time);

In your case:

...
WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(`opening_hours`, '$."wednesday-to"')) as time) > CAST('09:00' as time)
...
Alex
  • 16,739
  • 1
  • 28
  • 51