MongoDB: Find documents by the most recent item within nested array

Question

I have a collection with documents with next structure:

 {
    "Id" : 123
    "History" : [ 
        {
            "MachineId" : 1,
            "Time" : ISODate("2014-02-10T13:10:00Z"),
            "Status" : 0
        }, 
        {
            "MachineId" : 1,
            "Time" : ISODate("2014-02-10T13:10:44Z"),
            "Status" : 1
        }, 
        {
            "MachineId" : 2,
            "Time" : ISODate("2014-02-10T13:10:50Z"),
            "Status" : 2
        }
    ]
},
{
    "Id" : 345
    "History" : [ 
        {
            "MachineId" : 1,
            "Time" : ISODate("2014-02-10T13:10:00Z"),
            "Status" : 0
        }, 
        {
            "MachineId" : 1,
            "Time" : ISODate("2014-02-10T13:10:44Z"),
            "Status" : 1
        }
    ]
}

I have an input MachineId and Status and I want to find all documents that have the nested History item with given MachineId and Status, but this item must be the most recent one.

For example, for MachineId=1 and Status=1, I want get only the document with Id=345

I know how to query nested array elements, but here I need first to sort the history array to find the last added item. I have no idea how to do this in MongoDB.

Answer 1

Since you say you don't know how to approach this, I'll try and lead you through. This is well suited to the aggregation pipeline:

db.history.aggregate([
    // Match the item you want while you can still use an index
    {$match: { "History.MachineId" : 1, "Id" : 345 } },

    // ** Denormalise ** the array entries for additional processing
    {$unwind: "$History" },

    // Order things so the last time is first
    {$sort: { "History.Time": -1 }},

    // Filter out the first item per document
    {$group: { _id: "$Id", Time: {$first: "$History.Time" } }}
])

If you need more information than what is returned then you might take a look at this question for some pointers as to what to do to alter the shape of your results.

Answer 2

You can append

.sort({"History.Time":-1}).limit(1)

to your query.

If that doesn't meet your requirements, you can use aggregation framework, again having a $sort and $limit chain.