MySql join by json key and multiply quantity for each order item and get total price-CodePudding

I could not fiddle this out for hours now.

I would like to have the total price in one sql select.

Given is a json column where the key is the productId and the value is the quantity.

The customer can have multiple order items.

The quantity must be multiplied with net_price and tax_price. In SUM This gives the total price.

I can do this relational without json, but my preference is a json column.

I prepared an example to make it clear:

Given:

CREATE TABLE order_items (
  `customer_id` VARCHAR(26),
  `products` json
);

INSERT INTO order_items VALUES  ('01G51A4EK52RHB361SMXH2D5KL', '{"01G51A4EK52RHB361SMXH2D5KH": 10, "01G51A4EK52RHB361SMXH2D5KK": 20}');
INSERT INTO order_items VALUES  ('01G51A4EK52RHB361SMXH2D5KL', '{"01G51A4EK52RHB361SMXH2D5KH": 30}');
INSERT INTO order_items VALUES  ('01G51A4EK52RHB361SMXH2D5KL', '{"01G51A4EK52RHB361SMXH2D5KH": 30}');

CREATE TABLE product (
  `productId` VARCHAR(26),
  `net_price` INTEGER,
  `tax_price` INTEGER
);

INSERT INTO product VALUES  ('01G51A4EK52RHB361SMXH2D5KH', 100, 20);
INSERT INTO product VALUES  ('01G51A4EK52RHB361SMXH2D5KK', 200, 10);

What I have by now but it is incomplete:

SELECT
    JSON_UNQUOTE(
            JSON_EXTRACT(
                    JSON_KEYS(`products`),
                    CONCAT(
                            '$[',
                            ROW_NUMBER() OVER(PARTITION BY `products`) -1,
                            ']'
                        )
                )
        ) AS "productId",quantity
FROM order_items
         JOIN JSON_TABLE(
        products,
        '$.*' COLUMNS (
            quantity VARCHAR(50) PATH '$'
            )
    ) j
WHERE `order_items`.`customer_id` = '01G51A4EK52RHB361SMXH2D5KL';

DB-Fiddle: https://www.db-fiddle.com/f/reewoqUCQxeDLJb6zpb1RG/1

Could someone help me out here? Is this even possible? Thank you!

CodePudding user response：

Here's a solution to get the corresponding net_price and tax_price. I am not sure how you want to use them.

SELECT j.productId,
  JSON_UNQUOTE(JSON_EXTRACT(i.products, CONCAT('$."', j.productId, '"'))) AS quantity,
  p.net_price,
  p.tax_price
FROM order_items AS i
CROSS JOIN JSON_TABLE(JSON_KEYS(i.products),
    '$[*]' COLUMNS (
        productId VARCHAR(26) PATH '$'
    )
) AS j
JOIN product AS p USING (productId)
WHERE i.`customer_id` = '01G51A4EK52RHB361SMXH2D5KL';

Output given your sample data:

 ---------------------------- ---------- ----------- ----------- 
| productId                  | quantity | net_price | tax_price |
 ---------------------------- ---------- ----------- ----------- 
| 01G51A4EK52RHB361SMXH2D5KH | 30       |       100 |        20 |
| 01G51A4EK52RHB361SMXH2D5KH | 30       |       100 |        20 |
| 01G51A4EK52RHB361SMXH2D5KH | 10       |       100 |        20 |
| 01G51A4EK52RHB361SMXH2D5KK | 20       |       200 |        10 |
 ---------------------------- ---------- ----------- -----------

Calculating the total aggregate price:

SELECT SUM(
    JSON_UNQUOTE(JSON_EXTRACT(i.products, CONCAT('$."', j.productId, '"')))
    * (p.net_price   p.tax_price)
  ) AS total_price
FROM order_items AS i
CROSS JOIN JSON_TABLE(JSON_KEYS(i.products),
    '$[*]' COLUMNS (
        productId VARCHAR(26) PATH '$'
    )
) AS j
JOIN product AS p USING (productId)
WHERE i.`customer_id` = '01G51A4EK52RHB361SMXH2D5KL';

Output:

 ------------- 
| total_price |
 ------------- 
|       12600 |
 -------------