MySQL nested JSON column search and compare to with today's day

Question

I am using MySQL Version 5.7.28. I am having json data like below.

CREATE TABLE `week2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` smallint(1),
  `json` text ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO week2(id,type,json)
VALUES
    (121,1,'[{"weekdays":"Sunday"},{"weekdays":"Monday"},{"weekdays":"Tuesday"},{"weekdays":"Wednesday"},{"weekdays":"Thursday"},{"weekdays":"Friday"},{"weekdays":"Saturday"}]'),
    (122,1,'[{"weekdays":"Sunday"},{"weekdays":"Monday"}]'),
    (123,2,'[{"start_time":"08:00 AM","end_time":"10:00 PM"}]');

As you see, the json column has nested JSON data. so here i am looking to compare today's day ( which is Saturday and we are currently in between start time and end time)

Expected Result:

(121,1,'[{"weekdays":"Sunday"},{"weekdays":"Monday"},{"weekdays":"Tuesday"},{"weekdays":"Wednesday"},{"weekdays":"Thursday"},{"weekdays":"Friday"},{"weekdays":"Saturday"}]'),
(123,2,'[{"start_time":"08:00 AM","end_time":"10:00 PM"}]');

Answer 1

You need to detect day name (1) and time period (2), and combine those two conditions by OR operator at the end.

For (1) : Detect the current day's name by using DAYNAME() function and search whether exists in the JSON data containing weekday keys through use of JSON_CONTAINS function.

For (2) : TIME() function and CASTing strings to TIME data type might be used with a trick to add 12 hours iterations for the cases of PM type times.

So, consider using :

SELECT *
  FROM `week2`
 WHERE JSON_CONTAINS(`json`->>'$[*].weekdays', CONCAT('"',DAYNAME( NOW() ),'"')) = 1
    OR
    (
      TIME(ADDTIME(NOW(),"8:00:00")) >=       
      CASE WHEN INSTR(REPLACE(`json`->>'$[0].start_time',"12:00 AM","00:00 AM"),"PM")>0 
           THEN 
                CAST(CONCAT(MOD((TIME_FORMAT(REPLACE(`json`->>'$[0].start_time',"12:00 AM","00:00 AM"), "%T")+12),24),":00 AM") 
                  AS TIME) 
           ELSE
                CAST((REPLACE(`json`->>'$[0].start_time',"12:00 AM","00:00 AM")) AS TIME)
            END    
   AND 
      TIME(ADDTIME(NOW(),"8:00:00")) <=
           CASE WHEN INSTR(REPLACE(`json`->>'$[0].end_time',"12:00 AM","00:00 AM"),"PM")>0 
           THEN 
                CAST(CONCAT(MOD((TIME_FORMAT(REPLACE(`json`->>'$[0].end_time',"12:00 AM","00:00 AM"), "%T")+12),24),":00 AM") 
                  AS TIME) 
           ELSE
                CAST(REPLACE(`json`->>'$[0].end_time',"12:00 AM","00:00 AM") AS TIME)                  
            END)

Demo