Max and compare condition within an embedded document array

Question

I've got a collection schema like this, the dates are posted in an human readable format but they are stored as ISO dates:

{
    _id : ObjectId(...)
    statuses :
    [
        {
            status: "WIP",
            date: 9/09/2015 09:31:16.205,
        },
        {
            status: "Finished",
            date: 9/09/2015 10:31:16.205,
        }
    ]
}

I want to retrieve every collection document which it's most recent status (date field) has the "Finished" status set.

I was struggling how to do so in the lines ofdb.records.find({"statuses":{$elemMatch:{$max:date}}}) but I'm not able to accomplish the query properly as I don't realize how to compare then with the status field.

It may only be possible with the aggregation framework, but I'm even more lost. I'd like some guidance with this kind of query.

EDIT: My issue has to do more with retrieving the max date in the statuses array and then look at that document status in the same query.

EDIT: After some attempts, this is my closest answer.

db.records.aggregate([{$match : {"statuses.status":"Finished"}},{$unwind : "$statuses"},{$sort: { "date": -1}},{$limit: 1}])

possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection). And I still have that question open in a browser tab from the last 2 duplicates. Long forms of `.aggregate()` with `$unwind` in the answers given there, as you need to `$sort`. Also your dates appear to be "strings", which is not going to help you. — Blakes Seven, Sep 09 '15 at 09:13
@BlakesSeven The dates are stored in an ISO format as I remarked in the question, my main issue is how to retrieve the max date and then look at the status in the same query. — Miguel A. Arilla, Sep 09 '15 at 09:20

score 0 · Answer 1 · answered Sep 09 '15 at 13:21

Figured it out.

db.records.aggregate(
    [
        {
            $match : { "statuses.status" : "Finished" }
        },
        {
            $unwind: "$statuses"
        },
        {
            $sort: { "statuses.date":-1 }
        },
        {
            $group: {
                _id: "$_id",
                current_status: { $push: "$statuses" }
            }
        },
        {
            $match : { "current_status.status" : "Finished" }
        },
        {
            $project : { _id : 1}
        }
    ])

Max and compare condition within an embedded document array

1 Answers1