1

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!

2
  • Please be aware that MySQL and MariaDB are different products. In particular, they have independent implementations of JSON features, since they both developed JSON support after MariaDB forked from MySQL. I have edited your title and tags to clarify that you are using MariaDB, not MySQL. Jun 16 at 7:00
  • Got it, thanks a lot Bill
    – Khuzema
    Jun 16 at 11:37

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.