By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `Orders` (
`Id` int NOT NULL,
`Customer` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`Value` decimal(9,2) NOT NULL,
PRIMARY KEY (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `OrderItems` (
`Id` int NOT NULL,
`OrderId` int NOT NULL, -- FK to Order (Id)
`Qty` int NOT NULL,
`Price` decimal(9,0) NOT NULL,
`Total` decimal(9,2) NOT NULL,
PRIMARY KEY (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO Orders VALUES
(1, 'Joe BLoggs', 120.00),
(2, 'Sam Rockstar', 140.00),
(3, 'Jack Jones', 0.00);
INSERT INTO OrderItems VALUES
(1, 1, 3, 20, 60),
(2, 1, 2, 30, 60),
(3, 2, 2, 35, 70),
(4, 2, 1, 70, 70);
Records: 3 Duplicates: 0 Warnings: 0
Records: 4 Duplicates: 0 Warnings: 0
SELECT JSON_PRETTY(
JSON_OBJECT(
'Orders', JSON_ARRAYAGG(
JSON_OBJECT(
'Id', o.Id,
'Customer', o.Customer,
'Value', o.Value,
'Items', o.Items
)
)
)
) AS result
FROM (
SELECT Orders.Id, Orders.Customer, Orders.Value,
JSON_ARRAYAGG(
JSON_OBJECT(
'Id', OrderItems.Id,
'Qty', OrderItems.Qty,
'Price', OrderItems.Price,
'Total', OrderItems.Total
)
) AS Items
FROM Orders JOIN OrderItems ON Orders.Id = OrderItems.OrderId
GROUP BY Orders.Id
UNION
SELECT Orders.Id, Orders.Customer, Orders.Value,
JSON_ARRAY() AS Items
FROM Orders LEFT OUTER JOIN OrderItems ON Orders.Id = OrderItems.OrderId
WHERE OrderItems.OrderId IS NULL
) AS o;
| result |
|---|
| { "Orders": [ { "Id": 1, "Items": [ { "Id": 1, "Qty": 3, "Price": 20, "Total": 60.00 }, { "Id": 2, "Qty": 2, "Price": 30, "Total": 60.00 } ], "Value": 120.00, "Customer": "Joe BLoggs" }, { "Id": 2, "Items": [ { "Id": 3, "Qty": 2, "Price": 35, "Total": 70.00 }, { "Id": 4, "Qty": 1, "Price": 70, "Total": 70.00 } ], "Value": 140.00, "Customer": "Sam Rockstar" }, { "Id": 3, "Items": [], "Value": 0.00, "Customer": "Jack Jones" } ] } |