How to calculate quantity based on a boolean field and retrieve values from a JSON field in MySQL

Question

I'm working with a table called price_options that has the following fields: id, slots, list_slots, and quantity.

Issue Description

My objective is to calculate the quantity for each price option based on the boolean value of the slots field. If slots is set to true, I need to retrieve the quantity from the list_slots field, which contains JSON data structured like this: [{"start":"09:00","quantity":30},{"start":"09:30","quantity":30}]. Otherwise, I should use the quantity field directly.

1- SQL For Creating Table:

CREATE TABLE price_options (
    id INT UNSIGNED PRIMARY KEY,
    slots BOOLEAN,
    quantity INT,
    list_slots TEXT NULL
);

2- Adding data in table for case: 1

INSERT INTO price_options (slots, quantity, list_slots)
VALUES (TRUE, 0, '[{"start":"09:00","quantity":30},{"start":"09:30","quantity":30}]');

3 -Adding data in table for case: 2

INSERT INTO price_options (slots, quantity, list_slots) VALUES (FALSE, 30, NULL);

4- Required Output for Both Cases:

Case 1:

slots field is true, i want total quantity calculated from list_slots field.

Output should be (30+30) = 60

Case 2:

slots field is false, i want total quantity from quantity field. This time we will ignore list_slots field.

Output should be: 30

Note:

list_slots field type is text.
The MySQL version I'm using is 10.4.24-MariaDB (Xampp).

I hope it helps to understand the issue Any help or guidance on how to approach this would be greatly appreciated. Thank you.

Akina · Accepted Answer · 2023-06-14T19:47:12.280

CREATE TABLE price_options (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slots BOOLEAN,
    quantity INT,
    list_slots TEXT NULL
);
INSERT INTO price_options (slots, quantity, list_slots)
VALUES (TRUE, 0, '[{"start":"09:00","quantity":30},{"start":"09:30","quantity":30}]');
INSERT INTO price_options (slots, quantity, list_slots) VALUES (FALSE, 30, NULL);
SELECT * FROM price_options;

id	slots	quantity	list_slots
1	1	0	[{"start":"09:00","quantity":30},{"start":"09:30","quantity":30}]
2	0	30	null

SELECT price_options.id, SUM(jsontable.quantity) quantity
FROM price_options,
     JSON_TABLE(list_slots,
                '$[*]' COLUMNS (quantity INT PATH '$.quantity')) jsontable
WHERE slots
GROUP BY 1
UNION ALL
SELECT id, quantity
FROM price_options
WHERE NOT slots

id	quantity
1	60
2	30

fiddle

The MySQL version I'm using is 10.4.24-MariaDB (Xampp).

WITH RECURSIVE cte AS (
  SELECT id, list_slots, JSON_EXTRACT(list_slots, '$[0].quantity') quantity, 1 ordinality
  FROM price_options
  WHERE slots
  UNION ALL
  SELECT id, list_slots, JSON_EXTRACT(list_slots, CONCAT('$[', ordinality, '].quantity')), ordinality + 1
  FROM cte
  WHERE ordinality < JSON_LENGTH(list_slots)
)
SELECT id, SUM(quantity) quantity
FROM cte
GROUP BY 1
UNION ALL
SELECT id, quantity
FROM price_options
WHERE NOT slots