返回最后一个“真"每个组的价值 [英] Return the last "true" value for each group
问题描述
我有一个集合,其中的文档如下:
I have collection in which documents are like:
{
_id: ObjectId(),
user: ObjectId(),
studentName: String,
createdAt: Date,
isAbondoned: boolean
}
文档示例如下:
1-
{
"_id" : ObjectId("56cd2d36a489a5b875902f0e"),
"user" : ObjectId("56c4cafabd5f92cd78ae49d4"),
"studentName" : "Aman",
"createdAt" : ISODate("2016-02-24T04:10:30.486+0000"),
"isAbandoned" : true
}
2-
{
"_id" : ObjectId("56cd2dcda489a5b875902fcd"),
"user" : ObjectId("56c4cafabd5f92cd78ae49d4"),
"studentName" : "Aman",
"createdAt" : ISODate("2016-02-24T04:13:01.932+0000"),
"isAbandoned" : false
}
3-
{
"_id" : ObjectId("56cee51503b7cb7b0eda9c4c"),
"user" : ObjectId("56c85244bd5f92cd78ae4bc1"),
"studentName" : "Rajeev",
"createdAt" : ISODate("2016-02-25T11:27:17.281+0000"),
"isAbandoned" : true,
}
现在我想找到他们的最后一个createdAt"文档的isAbandoned"为真的学生列表.
Now I want to find the list of students for which their 'isAbandoned' is true for their last 'createdAt' document.
以上示例所需的输出是:
Required output for above example is:
{
"user" : ObjectId("56c85244bd5f92cd78ae4bc1"),
"studentName" : "Rajeev"
}
因为对于 studentName "Aman" max(createdAt) 是第二个文档,而 'isAbandoned' 是假的.
because for studentName "Aman" max(createdAt) is 2nd document and 'isAbandoned' is false for that.
推荐答案
最好的方法是使用聚合框架.您需要$group
用户"的文档并使用 $last
累加器运算符,但要使其工作,您需要使用 $sort
聚合管道操作符.要对文档进行排序,您需要同时考虑createdAt"字段和user"字段.
The best way to do this is using the aggregation framework. You need to $group
your documents by "user" and return the last document for each user using the $last
accumulator operator but for this to work, you need a preliminary sorting stage using the $sort
aggregation pipeline operator. To sort your documents, you need to consider both the "createdAt" field and the "user" field.
管道的最后一个阶段是$match
阶段,您只选择isAbandoned"等于 true
的最后那些文档.
The last stage in the pipeline is the $match
stage where you select only those last documents where "isAbandoned" equals true
.
db.students.aggregate([
{ "$sort": { "user": 1, "createdAt": 1 } },
{ "$group": {
"_id": "$user",
"last": { "$last": "$$ROOT" }
}},
{ "$match": { "last.isAbandoned": true } }
])
返回如下内容:
{
"_id" : ObjectId("56c85244bd5f92cd78ae4bc1"),
"last" : {
"_id" : ObjectId("56cee51503b7cb7b0eda9c4c"),
"user" : ObjectId("56c85244bd5f92cd78ae4bc1"),
"studentName" : "Rajeev",
"createdAt" : ISODate("2016-02-25T11:27:17.281Z"),
"isAbandoned" : true
}
}
为了得到预期的结果,我们需要使用$replaceRoot
3.4 版本开始的管道操作符,将嵌入的文档提升到顶层
To get the expected result, we need to use the $replaceRoot
pipeline operator starting from verion 3.4 to promote the embedded document to the top level
{
$replaceRoot: { newRoot: "$last" }
}
在旧版本中,您需要使用$project
聚合管道操作来重塑我们的文档.因此,如果我们通过以下阶段扩展我们的管道:
In older version, you need to use the $project
aggregation pipeline operation to reshape our documents. So if we extend our pipeline with the following stage:
{
"$project": {
"_id": "$last._id",
"user": "$last.user",
"studentName": "$last.studentName",
"createdAt": "$last.createdAt",
"isAbandoned": "$last.isAbandoned"
}}
它产生预期的输出:
{
"_id" : ObjectId("56cee51503b7cb7b0eda9c4c"),
"user" : ObjectId("56c85244bd5f92cd78ae4bc1"),
"studentName" : "Rajeev",
"createdAt" : ISODate("2016-02-25T11:27:17.281Z"),
"isAbandoned" : true
}
这篇关于返回最后一个“真"每个组的价值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!