compare values with JSON_EXTRACT

user_13465192 0
Viewed 828

I've got a strange issue when I try to compare values with JSON_EXTRACT.

Here is an extract of my column opening_hours in database :

{"1": {"end": {"max_hour": "1290", "min_hour": "840"}, "begin": {"max_hour": "720", "min_hour": "420"}}, 
 "2": {"end": {"max_hour": "1290", "min_hour": "840"}, "begin": {"max_hour": "720", "min_hour": "420"}}, 
 "3": {"end": {"max_hour": "1290", "min_hour": "840"}, "begin": {"max_hour": "720", "min_hour": "420"}}, 
 "4": {"end": {"max_hour": "1290", "min_hour": "840"}, "begin": {"max_hour": "720", "min_hour": "420"}}, 
 "5": {"end": {"max_hour": "1290", "min_hour": "840"}, "begin": {"max_hour": "720", "min_hour": "420"}}}

When I search if, for example, "720" is between the min_hour and max_hour of each part of the day (begin and end), I've got results :

SELECT *
FROM `service_provider`
WHERE ((JSON_EXTRACT(`opening_hours`, '$."3"."begin"."min_hour"') <= '720' 
        AND '720' <= JSON_EXTRACT(`opening_hours`, '$."3"."begin"."max_hour"')) 
      OR (JSON_EXTRACT(`opening_hours`, '$."3"."end"."min_hour"') <= '720' 
          AND '720' <= JSON_EXTRACT(`opening_hours`, '$."3"."end"."max_hour"')));

====> GOT SOME RESULTS !

But when I do the same thing with "1200", no result found :

SELECT *
FROM `service_provider`
WHERE ((JSON_EXTRACT(`opening_hours`, '$."3"."begin"."min_hour"') <= '1200' 
         AND '1200' <= JSON_EXTRACT(`opening_hours`, '$."3"."begin"."max_hour"')) 
      OR (JSON_EXTRACT(`opening_hours`, '$."3"."end"."min_hour"') <= '1200' 
           AND '1200' <= JSON_EXTRACT(`opening_hours`, '$."3"."end"."max_hour"')));

-----> NO RESULTS.

Yet it is well between "840" and "1290".

I noticed that only numbers before or equal at "720" gave results, so it doesn't take into account the second part of my query (after the OR). Why ? Thank you.

mysql sql json
1 Answers

The first thought I had was, are those JSON_EXTRACT final values returned as integer or string? And from my testing, it's returning string. So, when you do comparison using "<=" on string, it won't behave like numbers. Let's see your first query:

SELECT *
FROM `service_provider`
WHERE ((JSON_EXTRACT(`opening_hours`, '$."3"."begin"."min_hour"') <= '720' 
        AND '720' <= JSON_EXTRACT(`opening_hours`, '$."3"."begin"."max_hour"')) 
      OR (JSON_EXTRACT(`opening_hours`, '$."3"."end"."min_hour"') <= '720' 
          AND '720' <= JSON_EXTRACT(`opening_hours`, '$."3"."end"."max_hour"')));

This return result because you're using OR and the first condition of

(JSON_EXTRACT(`opening_hours`, '$."3"."begin"."min_hour"') <= '720' 
        AND '720' <= JSON_EXTRACT(`opening_hours`, '$."3"."begin"."max_hour"')

is true because of {"max_hour": "720", "min_hour": "420"} are both equal or smaller than 720. Well, more like "7" is bigger than "4" or similar to "7". What if you remove the first condition in OR like :

SELECT *
FROM `service_provider`
WHERE (JSON_EXTRACT(`opening_hours`, '$."3"."end"."min_hour"') <= '720' 
          AND '720' <= JSON_EXTRACT(`opening_hours`, '$."3"."end"."max_hour"'));

This won't return you anything since in the first comparison, "end"."min_hour" returned 840 hence "8" is bigger than "7" AND the second condition "end"."max_hour" value of 1290 being recognize as smaller because "1" is smaller than "7".

I think you got the idea. Since this is comparing string, it's always the first character in the value that being compared first.

A quick workaround to your current issue is to turn the JSON_EXTRACT value as integer. My suggestion:

SELECT *
FROM `service_provider`
WHERE ((JSON_EXTRACT(`opening_hours`, '$."3"."begin"."min_hour"')+0 <= 1200
         AND 1200 <= JSON_EXTRACT(`opening_hours`, '$."3"."begin"."max_hour"')+0) 
      OR (JSON_EXTRACT(`opening_hours`, '$."3"."end"."min_hour"')+0 <= 1200 
           AND 1200 <= JSON_EXTRACT(`opening_hours`, '$."3"."end"."max_hour"')+0));

Adding +0 at the end of the JSON_EXTRACT value will quickly turn the string to integer and you might as well just remove ' from '1200'.

If you want to look at the values returned from JSON extract and use it, you can move all of them into SELECT and use HAVING to do comparison like :

SELECT JSON_EXTRACT(`opening_hours`, '$."3"."begin"."min_hour"')+0 bmin,
       JSON_EXTRACT(`opening_hours`, '$."3"."begin"."max_hour"')+0 bmax,
       JSON_EXTRACT(`opening_hours`, '$."3"."end"."min_hour"')+0 emin,
       JSON_EXTRACT(`opening_hours`, '$."3"."end"."max_hour"')+0 emax
FROM `service_provider`
HAVING ((bmin <= 1200 and  1200 <= bmax) or (emin <= 1200 and 1200 <= emax))

Here is a fiddle of my tests

user_109106920
Related