MongoDB C# List the latest of all entries on a sub document

Question

Is it possible to list all the restaurants and their latest grade, if grades is a an array within a restaurant?

{
    "_id" : ObjectId("56bf7957b5e096fd06b755b2"),
    "grades" : [ 
        {
            "date" : ISODate("2014-11-15T00:00:00.000Z"),
            "grade" : "Z",
            "score" : 38
        }, 
        {
            "date" : ISODate("2014-05-02T00:00:00.000Z"),
            "grade" : "A",
            "score" : 10
        }, 
        {
            "date" : ISODate("2013-03-02T00:00:00.000Z"),
            "grade" : "A",
            "score" : 7
        }, 
        {
            "date" : ISODate("2012-02-10T00:00:00.000Z"),
            "grade" : "A",
            "score" : 13
        }
    ],
    "name" : "Brunos On The Boulevard",
}

I would want to get:

{
    "_id" : ObjectId("56bf7957b5e096fd06b755b2"),
    "grades" : [ 
        {
            "date" : ISODate("2014-11-15T00:00:00.000Z"),
            "grade" : "Z",
            "score" : 38
        }
    ],
    "name" : "Brunos On The Boulevard",
}

Explanation

The answer below uses the unwind operator. There's a really simple explanation of it on this answer, should anyone be confused by it as I was.

score 1 · Accepted Answer · answered Feb 14 '16 at 00:26

1

An option could be doing an aggregate with two operations, an Unwind which deconstructs your array field from the input documents to output a document for each element, and later a sort operation by date in descending order. This way you can get the result you are expected selecting the first element from the aggregate result:

 var result = collection.Aggregate()
                        .Unwind(e => e["grades"])
                        .SortByDescending(e=>e["grades.date"])
                        .FirstOrDefault();

answered Feb 14 '16 at 00:26

ocuenca

38,548
11
89
102

That seems to work. I say seems because I don't understand why. – BanksySan Feb 14 '16 at 00:35
Just found out I needed to add a `Match` element if I wanted to `ToList` it or I run out of memory! I didn't expect it to be that bigger query. – BanksySan Feb 14 '16 at 00:36
1

Just read [this answer](http://stackoverflow.com/a/16448763/442351) which explains unwind really well. – BanksySan Feb 14 '16 at 01:24
That's right, that operation tends to confuse a little bit, thanks for share the link – ocuenca Feb 14 '16 at 01:35

MongoDB C# List the latest of all entries on a sub document

1 Answers1