MySql通过json键连接并乘以每个订单项目的数量并得到总价

Question

我已经摆弄了好几个小时了

我想在一次sql查询中得到总价。

给定一个json列，其中键是productId，值是数量。

客户可以有多个订单项。

数量必须与net_price和tax_price相乘。在SUM中，这给出了总价。

我可以在没有json的情况下做这个关系，但我更喜欢json列。

我准备了一个例子来说明:

给定:

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);

我现在拥有的，但它是不完整的:

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

有人能帮我一下吗?这可能吗?谢谢你！

Answer 1

下面是获取相应net_price和tax_price的解决方案。我不确定你要如何使用它们。

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';

给出示例数据的输出:

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

---

计算总价格:

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';

输出:

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