Reputation: 11079
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.
Upvotes: 3
Views: 1894
Reputation: 151230
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.
Upvotes: 5
Reputation: 584
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.
Upvotes: 2