MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


5.7.33


create table service_provider (opening_hours json);

insert into service_provider values
('{"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"}}}')
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"')));


opening_hours


{"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"}}}


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


opening_hours


{"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"}}}


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"')));
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));


opening_hours


{"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"}}}


SELECT JSON_EXTRACT(`opening_hours`, '$."3"."begin"."min_hour"') bmin,
       JSON_EXTRACT(`opening_hours`, '$."3"."begin"."max_hour"') bmax,
       JSON_EXTRACT(`opening_hours`, '$."3"."end"."min_hour"') emin,
       JSON_EXTRACT(`opening_hours`, '$."3"."end"."max_hour"') emax
FROM `service_provider`;


bmin
bmax
emin
emax


"420"
"720"
"840"
"1290"


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


bmin
bmax
emin
emax


420
720
840
1290