How to find a time between nested Json

I have created a table called ticket_manager with the following structure:

Each time when the user based QR Code gets scanned it will add a new row (either when it’s a check-in it will add a new line to “checkIn with “check_in_$count” or a new line to checkOut with “check_out_$count” -> So for example "check_in_0 is a pair with "check_out_0"so..
p1 -> 2:00 – 4:00am; 10:00 – 11:00 am
p2 -> 3:00 – 4:00am;
p3 -> 9:00 – 9:30am

Now I want to make a MYSQL query to find all people which where at the event between a specific time for example: 03:00:00 am to 05:00:00 am.

I don’t really know the best way to loop through the json’s (and pair them together).
So far I know :
SELECT * FROM "wp_ticket_manager" WHERE "event_logs" BETWEEN '31.07.2021 03:00:00' AND '31.07.2021 05:00:00
to get all queries between normal fields (without pairs and json)
SELECT * FROM "wp_ticket_manager" WHERE JSON_EXTRACT(event_logs, '$.checkIn.check_in_0') BETWEEN '31.07.2021 03:00:00' AND '31.07.2021 05:00:00'
to check for a specific field (but didn’t worked)
Should I change the table layout to make it in any way easier?

Sample data:
mysql Ver 15.1 Distrib 10.4.18-MariaDB

CREATE TABLE IF NOT EXISTS `ticket_manager` (
            `id` int(11) unsigned NOT NULL auto_increment,
            `attendee_name` VARCHAR (255) NOT NULL, 
            `event_logs` text,
            PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `ticket_manager` (id, attendee_name, event_logs)
VALUES (1,'p1','{"checkIn":{"check_in_0":" 31.07.2021 02:00:00 am","check_in_1":" 31.07.2021 10:00:00 am"},"checkOut":{"check_out_0":" 31.07.2021 04:00:00 am","check_out_1":" 31.07.2021 11:00:00 am"}}'), (2,'p2','{"checkIn":{"check_in_0":" 31.07.2021 03:00:00 am",},
"checkOut":{"check_out_0":" 31.07.2021 04:00:00 am"}}'), (3,'p3','{"checkIn":{"check_in_0":" 31.07.2021 9:00:00 am",},
"checkOut":{"check_out_0":" 31.07.2021 9:30:00 am"}}');

(Desired output would be all queries between the specfic time in the request)

Answer

Parse your data using, for example:

WITH RECURSIVE
cte AS ( SELECT id, attendee_name, event_logs, 
                0 level,
                JSON_EXTRACT(event_logs, CONCAT('$.checkIn.check_in_0')) check_in,
                JSON_EXTRACT(event_logs, CONCAT('$.checkOut.check_out_0')) check_out
         FROM ticket_manager
         UNION ALL
         SELECT id, attendee_name, event_logs, 
                1 + level,
                JSON_EXTRACT(event_logs, CONCAT('$.checkIn.check_in_', 1 + level)) check_in,
                JSON_EXTRACT(event_logs, CONCAT('$.checkOut.check_out_', 1 + level)) check_out
         FROM cte
         HAVING check_in )

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=8f0974105b67a2606ea920ea0943c772

Then use extracted check_in and check_out values (pay attention – their format is not correct DATETIME format for MySQL/MariaDB).