MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (id INT,	open_hours JSON,	other VARCHAR(255));
INSERT INTO test VALUES
(1,	'["09:00-14:30", "19:00-21:30"]',	'jj'),
(2,	'["10:00-14:00"]',	'kk'),
(3,	'["01:00-04:00", "05:00-08:00", "10:00-15:00", "16:00-00:00"]',	'pp');

Records: 3  Duplicates: 0  Warnings: 0


SELECT test.id, 
       CAST(SUBSTRING_INDEX(jsontable.timerange, '-', 1) AS TIME) timestart, 
       CAST(SUBSTRING_INDEX(jsontable.timerange, '-', -1) AS TIME) timeend,
       test.other
FROM test
CROSS JOIN JSON_TABLE(test.open_hours,
                      '$[*]' COLUMNS (timerange VARCHAR(255) PATH '$')) jsontable
-- HAVING CURRENT_TIME BETWEEN timestart AND timeend
;


id
timestart
timeend
other


1
09:00:00
14:30:00
jj

1
19:00:00
21:30:00
jj

2
10:00:00
14:00:00
kk

3
01:00:00
04:00:00
pp

3
05:00:00
08:00:00
pp

3
10:00:00
15:00:00
pp

3
16:00:00
00:00:00
pp


SELECT test.id, 
       CAST(SUBSTRING_INDEX(jsontable.timerange, '-', 1) AS TIME) timestart, 
       CAST(SUBSTRING_INDEX(jsontable.timerange, '-', -1) AS TIME) timeend,
       test.other
FROM test
CROSS JOIN JSON_TABLE(test.open_hours,
                      '$[*]' COLUMNS (timerange VARCHAR(255) PATH '$')) jsontable
HAVING CURRENT_TIME BETWEEN timestart AND timeend;


id
timestart
timeend
other


3
05:00:00
08:00:00
pp

id	timestart	timeend	other
1	09:00:00	14:30:00	jj
1	19:00:00	21:30:00	jj
2	10:00:00	14:00:00	kk
3	01:00:00	04:00:00	pp
3	05:00:00	08:00:00	pp
3	10:00:00	15:00:00	pp
3	16:00:00	00:00:00	pp