MongoDB -根据前一项计算字段

Question

我有一个棘手的场景—我需要根据前一个字段的值计算额外的字段。我不知道如何以有效的方式做这件事。任何想法吗?

数据:

{
_id: 1,
score: 66,
created_at: "2021-04-01"
},
{
_id: 2,
score: 12,
created_at: "2021-04-03"
},
{
_id: 3,
score: 7,
created_at: "2021-04-06"
}

我想要达到的目标

{
_id: 1,
score: 66,
total_score: 66  // The most oldest item, so total score is the same as current score
created_at: "2021-04-01"
},
{
_id: 2,
score: 12,
total_score: 78  // Sum of current score and previous total_score
created_at: "2021-04-03"
},
{
_id: 3,
score: 7,
total_score: 85  // Sum of current score and previous total_score
created_at: "2021-04-06"
}

请赐教。

Answer 1

您可以尝试聚合查询，

• $lookup与管道和匹配大于_id查询以选择前一条记录
• $group取null，得到score
的和
• $arrayElemAt从查找结果中获取第一个元素
• $ifNull检查结果是否为null，然后返回0，否则返回当前值
• $add获取score的总数并从查找中返回分数

db.collection.aggregate([
{
$lookup: {
from: "collection",
let: { id: "$_id" },
pipeline: [
{ $match: { $expr: { $gt: ["$$id", "$_id"] } } },
{
$group: {
_id: null,
score: { $sum: "$score" }
}
}
],
as: "total_score"
}
},
{
$addFields: {
total_score: {
$add: [
"$score",
{
$ifNull: [
{ $arrayElemAt: ["$total_score.score", 0] },
0
]
}
]
}
}
}
])

游乐场