MongoDB C# Get latest document from group

Question

I have a group of statuses of pretend payments, each with a payment ID.

I want to get the latest status for each payment ID. The test I have creates some dummy data and then tried to query it. I've got this far:

[Test]
public void GetPaymentLatestStatuses()
{
    var client = new TestMongoClient();

    var database = client.GetDatabase("payments");

    var paymentRequestsCollection = database.GetCollection<BsonDocument>("paymentRequests");

    var statusesCollection = database.GetCollection<BsonDocument>("statuses");

    var payment = new BsonDocument { { "amount", RANDOM.Next(10) } };

    paymentRequestsCollection.InsertOne(payment);

    var paymentId = payment["_id"];

    var receivedStatus = new BsonDocument
                         {
                             { "payment", paymentId },
                             { "code", "received" },
                             { "date", DateTime.UtcNow }
                         };
    var acceptedStatus = new BsonDocument
                         {
                             { "payment", paymentId },
                             { "code", "accepted" },
                             { "date", DateTime.UtcNow.AddSeconds(-1) }
                         };
    var completedStatus = new BsonDocument
                          {
                              { "payment", paymentId },
                              { "code", "completed" },
                              { "date", DateTime.UtcNow.AddSeconds(-2) }
                          };

    statusesCollection.InsertMany(new [] { receivedStatus, acceptedStatus, completedStatus });

    var groupByPayments = new BsonDocument { {"_id", "$payment"} };

    var statuses = statusesCollection.Aggregate().Group(groupByPayments);

}

But now I'm at a brick wall.

Any poking in the right direction would help. I'm not sure that I'm not looking down the wrong end of the telescope.

Update

The following gives me the IDs of the correct documents.

var groupByPayments = new BsonDocument
                      {
                          { "_id", "$payment" },
                          { "id", new BsonDocument { { "$first", "$_id" } } }
                      };

var sort = Builders<BsonDocument>.Sort.Descending(document => document["date"]);

var statuses = statusesCollection.Aggregate().Sort(sort).Group(groupByPayments).ToList();

Can I get the full documents with a single query though, or do I have to now re-issue a command to get all the documents in that list?

Answer 1

Let's start with the easy way to get what you're trying to achieve. In the C# Driver 2.X of MongoDB you can find AsQueryable extension method that let's you create LINQ queries from your collections. This Linq provider was built over the Aggregation framework of MongoDB, so at the end your link query is going to be translated to an aggregation pipeline. So, if you have a class like this:

public class Status
{
  public ObjectId _id { get; set; }
  public ObjectId payment { get; set; }
  public string code { get; set; }
  public DateTime date { get; set; }
}

You can create a query like the following:

 var statusesCollection = database.GetCollection<Status>("statuses");
 var result= statusesCollection.AsQueryable()
                               .OrderByDescending(e=>e.date)
                               .GroupBy(e=>e.payment)
                               .Select(g=>new Status{_id =g.First()._id,
                                                     payment = g.Key,
                                                     code=g.First().code,
                                                     date=g.First().date
                                                    }
                                       )
                               .ToList();

Now you may wondering why I had to project the result to a new instance of Status class if I could get the same result calling First extension method from each group? Unfortunately that is not supported yet. One of the reason is because the Linq provider is using $first operation when it build the aggregation pipeline, and that is how $first operation works. Also, as you can see in the link a shared earlier,when you use $first in a $group stage, the $group stage should follow a $sort stage to have the input documents in a defined order.

---

Now, supposing you don't want to use Linq and you want to work creating the aggregation pipeline by yourself, you could do the following:

 var groupByPayments = new BsonDocument
                      {
                          { "_id", "$payment" },
                          { "statusId", new BsonDocument { { "$first", "$_id" } } },
                          { "code", new BsonDocument { { "$first", "$code" } } },
                          { "date", new BsonDocument { { "$first", "$date" } } }
                      };

var sort = Builders<BsonDocument>.Sort.Descending(document => document["date"]);

ProjectionDefinition<BsonDocument> projection = new BsonDocument
        {
            {"payment", "$_id"},
            {"id", "$statusId"},
            {"code", "$code"},
            {"date", "$date"},
        }; 
var statuses = statusesCollection.Aggregate().Sort(sort).Group(groupByPayments).Project(projection).ToList<BsonDocument>();

The advantage of this solution is that you get the data in one round trip, and the disadvantage is you have to project all the fields that you need.My conclusion would be if the document doesn't have many fields or you don't need all the fields from your document I would use this variant.