英文:
Need to mark paid installments in an array using mongoDB aggregation
问题
我想编写一个聚合函数，根据到期日排序分期付款并根据已支付金额标记它们为已支付。例如，对于这种情况，函数应返回：
现在，我可以使用$unwind和$sort函数对数组进行排序，如下所示：
我卡住的地方是如何将数组重新分组，以便它给我所需的结果。我只能在这里使用聚合。
英文:
I have a schema named orders which looks like this :
{
"_id" : ObjectId("5cd42f7b16c2654ea9138ece"),
"customerId" : ObjectId("5c8222109146d119ccc5243f"),
"orderAmount" : NumberInt(10000),
"paidAmount" : NumberInt(4000),
"installments" : [
{
"dueDate" : ISODate("2020-01-01"),
"amount" : NumberInt(2000)
},
{
"dueDate" : ISODate("2020-01-07"),
"amount" : NumberInt(6000)
},
{
"dueDate" : ISODate("2020-01-04"),
"amount" : NumberInt(2000)
}
]
}
I want to write an aggregation function that sorts the installments according to dueDate and mark them paid according to paidAmount. For example for this case the function should return
{
"_id" : ObjectId("5cd42f7b16c2654ea9138ece"),
"customerId" : ObjectId("5c8222109146d119ccc5243f"),
"orderAmount" : NumberInt(10000),
"paidAmount" : NumberInt(4000),
"installments" : [
{
"dueDate" : ISODate("2020-01-01"),
"amount" : NumberInt(2000),
"paid" : true
},
{
"dueDate" : ISODate("2020-01-04"),
"amount" : NumberInt(2000),
"paid" : true
},
{
"dueDate" : ISODate("2020-01-07"),
"amount" : NumberInt(6000),
"paid" : false
}
]
}
Now I can sort the array using the $unwind and $sort functions like this:
db.orders.aggregate([
{$unwind : "$installments"},
{$sort : {"dueDate" : 1}}
]);
What I am stuck on is how to group the array back so that it gives me the desired result. I can only use aggregation here.
答案1
得分: 0
You need to $group installments. But, if you need to put paid field with some logic, it's necessary to add extra pipeline stages.
ASSUMPTION
paidAmount value calculated by ordered installments.[].paid
paidAmount installments.[].paid
4000 <= 2000(t) 2000(t) 6000(f)
4000 <≠ 2000(t) 6000(f) 2000(f)
4000 <≠ 6000(f) 2000(f) 2000(f)
6000 <= 6000(t) 2000(f) 2000(f)
6000 <≠ 1000(t) 6000(f) 1000(f)
6000 <= 1000(t) 4000(t) 1000(t)
EXPLANATION paid:true|false LOGIC
We order installments and create an extra tmp field with installments' values (for the paid field).
For each installments itemi, we mark paid:true if paidAmount - sum(amount0 - i) >= 0.
db.orders.aggregate([
{
$unwind: "$installments"
},
{
$sort: {
"installments.dueDate": 1
}
},
{
$group: {
_id: "$_id",
orders: {
$first: "$$ROOT"
},
installments: {
$push: "$installments"
},
tmp: {
$push: "$installments"
}
}
},
{
$unwind: "$installments"
},
{
$addFields: {
"installments.paid": {
$cond: [
{
$gte: [
{
$reduce: {
input: {
$slice: [
"$tmp",
{
$sum: [
{
$indexOfArray: [
"$tmp",
"$installments"
]
},
1
]
}
]
},
initialValue: "$orders.paidAmount",
in: {
$sum: [
{
$multiply: [
"$$this.amount",
-1
]
},
"$$value"
]
}
}
},
0
]
},
true,
false
]
}
}
},
{
$group: {
_id: "$_id",
customerId: {
$first: "$orders.customerId"
},
orderAmount: {
$first: "$orders.orderAmount"
},
paidAmount: {
$first: "$orders.paidAmount"
},
installments: {
$push: "$installments"
}
}
}
])
英文:
You need to $group installments. But, if you need to put paid field with some logic, it's necessary to add extra pipeline stages.
ASSUMPTION
paidAmount value calculated by ordered installments.[].paid
paidAmount installments.[].paid
4000 <= 2000(t) 2000(t) 6000(f)
4000 <≠ 2000(t) 6000(f) 2000(f)
4000 <≠ 6000(f) 2000(f) 2000(f)
6000 <= 6000(t) 2000(f) 2000(f)
6000 <≠ 1000(t) 6000(f) 1000(f)
6000 <= 1000(t) 4000(t) 1000(t)
EXPLANATION paid:true|false LOGIC
We order installments and create extra tmp field with installments value (for paid field).
For each installments item<sub> i</sub>, we mark paid:true if paidAmount - sum(amount<sub>0 - i</sub>) >= 0.
db.orders.aggregate([
{
$unwind: "$installments"
},
{
$sort: {
"installments.dueDate": 1
}
},
{
$group: {
_id: "$_id",
orders: {
$first: "$$ROOT"
},
installments: {
$push: "$installments"
},
tmp: {
$push: "$installments"
}
}
},
{
$unwind: "$installments"
},
{
$addFields: {
"installments.paid": {
$cond: [
{
$gte: [
{
$reduce: {
input: {
$slice: [
"$tmp",
{
$sum: [
{
$indexOfArray: [
"$tmp",
"$installments"
]
},
1
]
}
]
},
initialValue: "$orders.paidAmount",
in: {
$sum: [
{
$multiply: [
"$$this.amount",
-1
]
},
"$$value"
]
}
}
},
0
]
},
true,
false
]
}
}
},
{
$group: {
_id: "$_id",
customerId: {
$first: "$orders.customerId"
},
orderAmount: {
$first: "$orders.orderAmount"
},
paidAmount: {
$first: "$orders.paidAmount"
},
installments: {
$push: "$installments"
}
}
}
])
答案2
得分: 0
尝试这个：
db.yourCollectionName.aggregate([
{ $unwind: '$installments' },
{ $sort: { 'installments.dueDate': 1 } },
{ $addFields: { 'installments.paid': { $cond: [{ $lte: ["$installments.amount", "$paidAmount"] }, true, false] } } },
{ $group: { _id: '$id', data: { $first: '$$ROOT' }, installments: { $push: '$installments' } } },
{ $addFields: { 'data.installments': '$installments' } },
{ $replaceRoot: { newRoot: "$data" } }
])
集合数据：
/* 1 */
{
"_id": ObjectId("5cd42f7b16c2654ea9138ece"),
"customerId": ObjectId("5c8222109146d119ccc5243f"),
"orderAmount": 10000,
"paidAmount": 4000,
"installments": [
{
"dueDate": ISODate("2020-01-01T21:21:20.202Z"),
"amount": 2000
},
{
"dueDate": ISODate("2020-01-07T21:27:20.202Z"),
"amount": 6000
},
{
"dueDate": ISODate("2020-01-04T21:24:20.202Z"),
"amount": 2000
}
]
}
结果：
/* 1 */
{
"_id": ObjectId("5cd42f7b16c2654ea9138ece"),
"customerId": ObjectId("5c8222109146d119ccc5243f"),
"orderAmount": 10000,
"paidAmount": 4000,
"installments": [
{
"dueDate": ISODate("2020-01-01T21:21:20.202Z"),
"amount": 2000,
"paid": true
},
{
"dueDate": ISODate("2020-01-04T21:24:20.202Z"),
"amount": 2000,
"paid": true
},
{
"dueDate": ISODate("2020-01-07T21:27:20.202Z"),
"amount": 6000,
"paid": false
}
]
}
参考文档：聚合管道
英文:
Try this :
db.yourCollectionName.aggregate([{ $unwind: '$installments' },{ $sort: { 'installments.dueDate': 1 } },
{ $addFields: { 'installments.paid': { $cond: [{ $lte: ["$installments.amount", '$paidAmount'] }, true, false] } } },
{ $group: { _id: '$id', data: { $first: '$$ROOT' }, installments: { $push: '$installments' } } },
{ $addFields: { 'data.installments': '$installments' } },
{ $replaceRoot: { newRoot: "$data" } }])
Collection Data :
/* 1 */
{
"_id" : ObjectId("5cd42f7b16c2654ea9138ece"),
"customerId" : ObjectId("5c8222109146d119ccc5243f"),
"orderAmount" : 10000,
"paidAmount" : 4000,
"installments" : [
{
"dueDate" : ISODate("2020-01-01T21:21:20.202Z"),
"amount" : 2000
},
{
"dueDate" : ISODate("2020-01-07T21:27:20.202Z"),
"amount" : 6000
},
{
"dueDate" : ISODate("2020-01-04T21:24:20.202Z"),
"amount" : 2000
}
]
}
Result :
/* 1 */
{
"_id" : ObjectId("5cd42f7b16c2654ea9138ece"),
"customerId" : ObjectId("5c8222109146d119ccc5243f"),
"orderAmount" : 10000,
"paidAmount" : 4000,
"installments" : [
{
"dueDate" : ISODate("2020-01-01T21:21:20.202Z"),
"amount" : 2000,
"paid" : true
},
{
"dueDate" : ISODate("2020-01-04T21:24:20.202Z"),
"amount" : 2000,
"paid" : true
},
{
"dueDate" : ISODate("2020-01-07T21:27:20.202Z"),
"amount" : 6000,
"paid" : false
}
]
}
Ref : aggregation-pipeline
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论