I use MongoDB version 7. I store the payments data with order and buyer information. I have a few payments in a collection with order and buyer information, which I group by order id to the next JSON result:
{
"incomeCash": [
{
"_id": "64d5fe21fb5b6271ce7934a1",
"buyer": {
"title": "buyer1",
"key": "64234a2711d053037a0362d1"
},
"key": "64d5fe21fb5b6271ce7934a1", //order 1 key
"title": "ORDER 1",
"payments": [
{
"date": "2023-07-15T00:00:00.000Z",
"amount": "1.01"
}
],
"total": "1.01",
"direction": "in"
},
{
"_id": "6451db5cd001097cb1f27651",
"buyer": {
"title": "buyer1",
"key": "64234a2711d053037a0362d1"
},
"key": "6451db5cd001097cb1f27651", //order 2 key
"title": "ORDER 2",
"payments": [
{
"date": "2023-05-08T00:00:00.000Z",
"amount": "1.01"
},
{
"date": "2023-05-18T00:00:00.000Z",
"amount": "2.02"
},
{
"date": "2023-07-15T00:00:00.000Z",
"amount": "3.03"
}
],
"total": "6.06",
"direction": "in"
}
]
}
So my question is how to group by buyer.key
all orders with summarized payments by date, I what to get next JSON style:
[
{
"_id": "64234a2711d053037a0362d1",
"key": "64234a2711d053037a0362d1", //buyer key
"title": "buyer 1",
"subLevels": [
{
"key": "64d5fe21fb5b6271ce7934a1", //order 1 key
"title": "ORDER 1",
"payments": [
{
"date": "2023-07-15T00:00:00.000Z",
"amount": "1.01"
}
],
"total": "1.01",
"direction": "in"
},
{
"key": "6451db5cd001097cb1f27651", //order 2 key
"title": "ORDER 2",
"payments": [
{
"date": "2023-05-08T00:00:00.000Z",
"amount": "1.01"
},
{
"date": "2023-05-18T00:00:00.000Z",
"amount": "2.02"
},
{
"date": "2023-07-15T00:00:00.000Z",
"amount": "3.03"
}
],
"total": "6.06",
"direction": "in"
}
],
"total": 7.07,
"payments": [
{
"date": "2023-05-08T00:00:00.000Z",
"amount": "1.01"
},
{
"date": "2023-05-18T00:00:00.000Z",
"amount": "2.02"
},
{
"date": "2023-07-15T00:00:00.000Z", //group by date
"amount": "4.04" //sum by date
}
]
}
]
P.S. All amounts and totals are in decimal type, at the moment I try to use $group
with $accumulator
and function, but not all amounts are correctly summed, like:
{
"date": "2023-05-08T00:00:00.000Z",
"amount": "1.01"
},
{
"date": "2023-05-18T00:00:00.000Z",
"amount": "2.02"
},
{
"date": "2023-07-15T00:00:00.000Z",
"amount": "NumberDecimal(\"1.01\")NumberDecimal(\"3.03\")"
}
Maybe there is another variant of sum payments by date without function and group with accumulator?
Think that it is a complex query that needs to unwind and group the document multiple times.
$unwind
- Deconstruct the payments
array into multiple documents.
$group
- Group by buyer.key
and payments.date
to perform sum. And keep the original document in the root
field.
unwind
- Deconstruct the root
array into multiple documents.
$group
- Group by root._id
so that the output document should be similar as the original document.
$group
- Group by buyer.key
.
$set
- Sort the element in the subLevels
and payments
array by the date
field. The special case is that requires additional steps to remove the duplicate element from the payments
array before sorting.
db.incomeCash.aggregate([
{
$unwind: "$payments"
},
{
$group: {
_id: {
buyerKey: "$buyer.key",
date: {
$toDate: "$payments.date"
}
},
amount: {
$sum: {
$toDecimal: "$payments.amount"
}
},
root: {
$push: "$$ROOT"
}
}
},
{
$unwind: "$root"
},
{
$group: {
_id: "$root._id",
buyer: {
$first: "$root.buyer"
},
key: {
$first: "$root.key"
},
title: {
$first: "$root.title"
},
payments: {
$push: "$root.payments"
},
sumPayments: {
$addToSet: {
date: "$_id.date",
amount: "$amount"
}
},
total: {
$first: "$root.total"
},
direction: {
$first: "$root.direction"
}
}
},
{
$group: {
_id: "$buyer.key",
title: {
$first: "$buyer.title"
},
subLevels: {
$addToSet: {
key: "$key",
title: "$title",
payments: "$payments",
total: "$total",
direction: "$direction"
}
},
total: {
$sum: {
$toDecimal: "$total"
}
},
payments: {
$push: "$sumPayments"
}
}
},
{
$set: {
subLevels: {
$sortArray: {
input: "$subLevels",
sortBy: {
"payment.date": 1
}
}
},
payments: {
$sortArray: {
input: {
$reduce: {
input: "$payments",
initialValue: [],
in: {
$let: {
vars: {
elem: {
$concatArrays: [
"$$this",
"$$value"
]
}
},
in: {
$setUnion: "$$elem"
}
}
}
}
},
sortBy: {
date: 1
}
}
}
}
}
])