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
}
}
}
}
}
])