Checking if a specific date is closed in a JSON data stored within a text field in MariaDB

Question

Problem Description

I'm currently working on a complex stored procedure that involves a table called "price_options_test". Within this table, there is a text field named "close_days" that stores closed days in JSON format. I need assistance with implementing a query that can determine if a specific date is a closed day.

For instance, if I provide the date "2023-06-21" as input to the query, I would like it to convert the date to the corresponding day name (e.g., "wednesday"). Subsequently, the query should check the "close_days" field to determine if the day is closed e.g. "wednesday": true. If the day is closed, the query should return true or 1; otherwise, it should return false or 0.

Test Case:

• Date: "2023-06-21"
• ID: 1
• "close_days" JSON value: {"monday": false, "tuesday": false, "wednesday": true, "thursday": false, "friday": false, "saturday": false, "sunday": false}

The expected result is that the query should return true. This is because the provided date corresponds to "wednesday," and in the JSON object, the value for "wednesday" is true.

What I'm trying

Following condition is not working:

JSON_EXTRACT(close_days, CONCAT('$.', DATE_FORMAT(startDate, '%W'))) IS NULL OR JSON_EXTRACT(close_days, CONCAT('$.', DATE_FORMAT(startDate, '%W'))) = false)

Required Output

I want true or false based on date.

---

MariaDB Query & Sample Data

CREATE TABLE `price_options_test` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `slots` tinyint(1) DEFAULT NULL,
  `close_days` text DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `list_slots` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `price_options_test` (`id`, `slots`, `close_days`, `quantity`, `list_slots`) VALUES
(1, 1, '{"monday": false, "tuesday": false, "wednesday": true, "thursday": false, "friday": false, "saturday": false, "sunday": false}', 0, '[{"start": "09:00", "quantity": 30}, {"start": "09:30", "quantity": 30}]'),
(2, 0, '{"monday": false, "tuesday": false, "wednesday": true, "thursday": false, "friday": false, "saturday": false, "sunday": false}', 30, NULL);

Important Note

• 1- close_days field type is text, so JSON functions may not apply. directly.
• The MariaDB version I'm using is 10.4.24-MariaDB (Xampp).

---

I would greatly appreciate any guidance or examples on how to accomplish this task efficiently within a stored procedure. Thank you!

Answer 1

Test Case:

Date: "2023-06-21"

ID: 1

The expected result is that the query should return true.

SET @date := '2023-06-21', @id := 1;

SELECT JSON_EXTRACT(close_days, CONCAT('$.', LOWER(DAYNAME(@date))))
FROM price_options_test
WHERE id = @id;

JSON_EXTRACT(close_days, CONCAT('$.', LOWER(DAYNAME(@date))))
true

fiddle