如何利用C#和Mongo实现基于日期的最后一项过滤和获取

Question

这是json文档的片段，其中包含不同部门多名学生刷卡的记录。每个学生在进入教室时将有多个条目。查询需要根据学生ids和部门名称的列表获取最新信息。

{ 
[
 {
      "studentid"; "stu-1234",
      "dept" : "geog",
      "teacher_id" : 1, 
      "Carddetails": 
     { 
        "LastSwipeTimestamp": "2021-11-25T10:50:00.5230694Z"
     }
 },

 {
      "studentid"; "stu-1234",
      "dept" : "geog",
      "teacher_id" : 2, 
      "Carddetails": 
     { 
        "LastSwipeTimestamp": "2021-11-25T11:50:00.5230694Z"
     }
 },
 {
      "studentid"; "stu-abc",
      "dept" : "geog",
      "teacher_id" : 11, 
      "Carddetails": 
     { 
        "LastSwipeTimestamp": "2021-11-25T09:15:00.5230694Z"
     }
 },
 {
      "studentid"; "stu-abc",
      "dept" : "geog",
      "teacher_id" : 21, 
      "Carddetails": 
     { 
        "LastSwipeTimestamp": "2021-11-25T11:30:00.5230694Z"
     }
 }
]
}

从上面的数据中，查询需要根据studentID: stu-abc和stu-1234上一张刷卡时间戳从geog部门获取它们。在这种情况下，将分别是stu-abc: 2021-11-25T11:30:00.5230694Z和stu-1234:2021-11-25T11:50:00.5230694Z。

到目前为止，这是我的代码

string [] students = {'stu-abc', 'stu-1234'}
string dept = "geog";
var filter = Builders<BsonDocument>.Filter.In("studentid", students )
                    & Builders<BsonDocument>.Filter.Eq("dept", dept);

_collections.Find(filter).Sort(Builders<BsonDocument>.Sort.Ascending("{\"LastSwipeTimestamp\":-1}")).FirstOrDefault()

但这只给了我一张唱片stu-1234:2021-11-25T11:50:00.5230694Z

我怎么能两者兼得？

Answer 1

从您的需求来看，我认为聚合管道更合适。

[{
    $match: {
        "studentid": {
            "$in": [
                "stu-abc",
                "stu-1234"
            ]
        },
        "dept": "geog"
    }
}, {
    $sort: {
        "Carddetails.LastSwipeTimestamp": -1
    }
}, {
    $group: {
        "_id": {
            "studentid": "$studentid",
            "dept": "$dept"
        },
        "Carddetails": {
            $first: "$Carddetails"
        }
    }
}, {
    $project: {
        _id: 0,
        "studentid": "$_id.studentid",
        "dept": "$_id.dept",
        "Carddetails": "$Carddetails"
    }
}]

蒙戈游乐场样本

将MongoDB查询转换为Mongo .NET驱动程序如下：

string[] students = { "stu-abc", "stu-1234" };
string dept = "geog";

var pipeline = new BsonDocument[]
{
    new BsonDocument("$match",
    new BsonDocument
    {
        { "studentid",
            new BsonDocument("$in",
                BsonArray.Create(students))
            },
            { "dept", dept }
        }),
    new BsonDocument("$sort",
    new BsonDocument("Carddetails.LastSwipeTimestamp", -1)),
    new BsonDocument("$group",
        new BsonDocument
        {
            { "_id",
                new BsonDocument
                {
                    { "studentid", "$studentid" },
                    { "dept", "$dept" }
                } 
            },
            { "Carddetails",
                new BsonDocument("$first", "$Carddetails") 
            }
        }
    ),
    new BsonDocument("$project",
        new BsonDocument
        {
            { "_id", 0 },
            { "studentid", "$_id.studentid" },
            { "dept", "$_id.dept" },
            { "Carddetails", "$Carddetails" }
        }
    )
};

var result = collection.Aggregate<BsonDocument>(pipeline)
    .ToList();

Console.WriteLine(result.ToJson());

输出

​

​