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?

ocuenca · Accepted Answer · 2016-02-15T15:06:17.480

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.

score 1 · Answer 2 · answered Feb 14 '16 at 03:22

This is how I've achieved it. There has to be a better way though.

[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" },
                              { "id", new BsonDocument { { "$first", "$_id" } } }
                          };

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

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

    var statusIds = statuses.Select(x => x["id"]);

    var completedStatusDocumentsFilter =
        Builders<BsonDocument>.Filter.Where(document => statusIds.Contains(document["_id"]));

    var statusDocuments = statusesCollection.Find(completedStatusDocumentsFilter).ToList();

    foreach (var status in statusDocuments)
    {
        Assert.That(status["code"].AsString, Is.EqualTo("completed"));
    }
}

scaryman · Answer 3 · 2017-08-25T20:17:24.327

There has to be a better way though.

As of 2.5.3, you can access the current group inside of an aggregation. This lets us build a generic accessor that will retrieve the first element from a grouping via a native mongo query.

First, a helper class for Deserialization. KeyValuePair<TKey,TValue> is sealed, so we roll our own.

    /// <summary>
    /// Mongo-ified version of <see cref="KeyValuePair{TKey, TValue}"/>
    /// </summary>
    class InternalKeyValuePair<T, TKey>
    {
        [BsonId]
        public TKey Key { get; set; } 
        public T Value { get; set; }
    }
    //you may not need this method to be completely generic, 
    //but have the sortkey be the same helps
    interface IDateModified
    {
        DateTime DateAdded { get; set; }
    }
    private List<T> GroupFromMongo<T,TKey>(string KeyName) where T : IDateModified
    {
        //mongo linq driver doesn't support this syntax, so we make our own bsondocument. With blackjack. And Hookers. 
        BsonDocument groupDoc = MongoDB.Bson.BsonDocument.Parse(@"
         {
                _id: '$" + KeyName + @"',
                Value: { '$first': '$$CURRENT' }
        }");
        //you could use the same bsondocument parsing trick to get a generic 
        //sorting key as well as a generic grouping key, or you could use
        //expressions and lambdas and make it...perfect.
        SortDefinition<T> sort = Builders<T>.Sort.Descending(document => document.DateAdded);
        List<BsonDocument> intermediateResult = getCol<T>().Aggregate().Sort(sort).Group(groupDoc).ToList();
        InternalResult<T, TKey>[] list = intermediateResult.Select(r => MongoDB.Bson.Serialization.BsonSerializer.Deserialize<InternalResult<T, TKey>>(r)).ToArray();
        return list.Select(z => z.Value).ToList();
    }

Okay..I genericized it with some help from https://stackoverflow.com/a/672212/346272

    /// <summary>
    /// Mongo-ified version of <see cref="KeyValuePair{TKey, TValue}"/>
    /// </summary>
    class MongoKeyValuePair<T, TKey>
    {
        [BsonId]
        public TKey Key { get; set; }
        public T Value { get; set; }
    }
    private MongoKeyValuePair<T, TKey>[] GroupFromMongo<T, TKey>(Expression<Func<T, TKey>> KeySelector, Expression<Func<T, object>> SortSelector)
    {
        //mongo linq driver doesn't support this syntax, so we make our own bsondocument. With blackjack. And Hookers. 
        BsonDocument groupDoc = MongoDB.Bson.BsonDocument.Parse(@"
         {
                _id: '$" + GetPropertyName(KeySelector) + @"',
                Value: { '$first': '$$CURRENT' }
        }");
        SortDefinition<T> sort = Builders<T>.Sort.Descending(SortSelector);
        List<BsonDocument> groupedResult = getCol<T>().Aggregate().Sort(sort).Group(groupDoc).ToList();
        MongoKeyValuePair<T, TKey>[] deserializedGroupedResult = groupedResult.Select(r => MongoDB.Bson.Serialization.BsonSerializer.Deserialize<MongoKeyValuePair<T, TKey>>(r)).ToArray();
        return deserializedGroupedResult;
    }

    /* This was my original non-generic method with hardcoded strings, PhonesDocument is an abstract class with many implementations */
    public List<T> ListPhoneDocNames<T>() where T : PhonesDocument
    {
        return GroupFromMongo<T,String>(z=>z.FileName,z=>z.DateAdded).Select(z=>z.Value).ToList();
    }


    public string GetPropertyName<TSource, TProperty>(Expression<Func<TSource, TProperty>> propertyLambda)
    {
        Type type = typeof(TSource);

        MemberExpression member = propertyLambda.Body as MemberExpression;
        if (member == null)
            throw new ArgumentException(string.Format(
                "Expression '{0}' refers to a method, not a property.",
                propertyLambda.ToString()));

        PropertyInfo propInfo = member.Member as PropertyInfo;
        if (propInfo == null)
            throw new ArgumentException(string.Format(
                "Expression '{0}' refers to a field, not a property.",
                propertyLambda.ToString()));

        if (type != propInfo.ReflectedType &&
            !type.IsSubclassOf(propInfo.ReflectedType))
            throw new ArgumentException(string.Format(
                "Expresion '{0}' refers to a property that is not from type {1}.",
                propertyLambda.ToString(),
                type));

        return propInfo.Name;
    }

For bonus points, you can now easily do any of mongos other grouping operations without fighting the linq helpers. See https://docs.mongodb.com/manual/reference/operator/aggregation/group/ for all available grouping operations. Let's add a count.

    class MongoKeyValuePair<T, TKey>
    {
        [BsonId]
        public TKey Key { get; set; }
        public T Value { get; set; }
        public long Count { get; set; }
    }

        BsonDocument groupDoc = MongoDB.Bson.BsonDocument.Parse(@"
         {
                _id: '$" + GetPropertyName(KeySelector) + @"',
                Value: { '$first': '$$CURRENT' },
                Count: { $sum: 1 }
        }");

run the aggregation the exact same as before and your count property will be filled in with the amount of documents matching your groupkey. Neat!

score 0 · Answer 4 · edited Aug 01 '22 at 07:43

Building on the accepted answer there are times where you need to specify a filter that can't be expressed using the Linq IQueryable interface but you also don't want to resort to hand writing BSON and then having to also cast the BSON back to your object. You can combine the two examples and get a bit of a best of both worlds. Still wish you could just return g.First but this also works.

var statusesCollection = database.GetCollection<Status>("statuses");
var filter = Builders<Status>.Filter.GeoWithinCenterSphere(x => x.LongLatField, longitude, latitude, radians);
var res = await statusesCollection.Aggregate().Match(filter).Group(x => x.PersistantId,
            g=>new Status{_id =g.First()._id,
                         payment = g.Key,
                         code=g.First().code,
                         date=g.First().date
                        }
           ))
            .ToListAsync();

score 0 · Answer 5 · answered May 04 '23 at 10:07

Building on top of ocuenca's answer: As of Mongo C# driver version 2.17 it is possible to use First() directly inside the Group() statement:

var statusesCollection = database.GetCollection<Status>("statuses");
var result= statusesCollection.AsQueryable()
                              .OrderByDescending(e=>e.date)
                              .Group(e => e.payment, g => g.First())
                              .ToList();

Hence, you do not have to create a new instance of Status inside the projection.

In the release notes of MongoDB C# Driver they write:

Support for $topN and related accumulators in $group aggregation stage

https://mongodb.github.io/mongo-csharp-driver/2.17/what_is_new/

I just tried to use First() as an accumulator and it worked.

MongoDB C# Get latest document from group

Update

5 Answers5

Linked