CODEWITHSUNDEEP
mongodb
Will
Will

Reputation: 8631

Mongo query to return latest entry

Hi I have the following data structure in mongodb:

{
"_id" : "4087322f-1ad0-4595-935f-b41ef6d87306",
"lastModifiedDate" : ISODate("2016-11-22T20:48:05.904Z"),
"notes" : " I do not like him that much",
"peopleId" : "1121",
"status" : "asses"
}

I wish to query the data such that it will return the latest value for each peopleId which also matches a chosen status. I've been looking into $group however doing the equal status is causing some issue.

I have got as far as grouping the values by peopleId using:

.aggregate([      
            {$group : {_id : "$peopleId", "allData" : {$push : "$$ROOT"}}},
            {$sort : { lastModifiedDate : -1}}     
           ]).pretty()

To give give me these results:

{
    "_id" : "1123",
    "allData" : [
        {
            "_id" : "c9d6a6ce-104d-414a-8d89-512e556d7aba",
            "lastModifiedDate" : ISODate("2016-11-22T20:55:35.662Z"),
            "notes" : "He's in!",
            "peopleId" : "1123",
            "status" : "done"
        },
        {
            "_id" : "14e522d8-6cae-42ee-ad52-d5cf3cae0c29",
            "lastModifiedDate" : ISODate("2016-11-22T20:56:46.128Z"),
            "notes" : "He's in!",
            "peopleId" : "1123",
            "status" : "asses"
        }
    ]
}
{
    "_id" : "1121",
    "allData" : [
        {
            "_id" : "d2c3e5da-8696-4dcc-a5cd-1f2657f0192c",
            "lastModifiedDate" : ISODate("2016-11-22T20:46:35.097Z"),
            "notes" : " I do not like him",
            "peopleId" : "1121",
            "status" : "pending"
        },
        {
            "_id" : "4087322f-1ad0-4595-935f-b41ef6d87306",
            "lastModifiedDate" : ISODate("2016-11-22T20:48:05.904Z"),
            "notes" : " I do not like him that much",
            "peopleId" : "1121",
            "status" : "asses"
        },
        {
            "_id" : "be4fa5bd-da8c-4c1a-9010-6a86afd11dbc",
            "lastModifiedDate" : ISODate("2016-11-22T20:49:27.268Z"),
            "notes" : "He's in!\nTue Nov 22 12:49:27 PST 2016 He's TF OUT",
            "peopleId" : "1121",
            "status" : "done"
        }
    ]
}

From this I need to extract the most recent entry based on lastModifiedDate and then check that is matches a given status.

Upvotes: 0

Views: 110

Answers (2)

notionquest
notionquest

Reputation: 39166

Here are the solutions.

Solution 1:-

If you want all the fields in the collection in the output.

db.collection.aggregate([
    {$match : {"status" : "asses"}},
    {$sort : {peopleId : 1, lastModifiedDate : -1}},
    {$group : {_id : "$peopleId", "allData" : {$push : "$$ROOT"}}},
    {$project: {_id :0, allData : {$slice : ["$allData", 0, 1]}}}
]);

Output:-

{
    "allData" : [ 
        {
            "_id" : ObjectId("5834c004ba41f1f22e600c7f"),
            "lastModifiedDate" : ISODate("2016-11-24T20:48:05.904Z"),
            "notes" : " I do not like him that much",
            "peopleId" : "1",
            "status" : "asses"
        }
    ]
}

Solution 2:-

If you don't want all the fields from the collection in the output.

db.collection.aggregate([
    {$match : {"status" : "asses"}},
    {$sort : {peopleId : 1, lastModifiedDate : -1}},
    {$group : {_id : "$peopleId", lastModDate : {$first : "$lastModifiedDate"}}}
])

Output:-

{
    "_id" : "1",
    "lastModDate" : ISODate("2016-11-24T20:48:05.904Z")
}

Upvotes: 1

satish chennupati
satish chennupati

Reputation: 2650

i created a collection with following docs

{ "_id" : "4087322f-1ad0-4595-935f-b41ef6d87306", "lastModifiedDate" : ISODate("2016-11-22T20:48:05.904Z"), "notes" : " I do not like him that much",
"peopleId" : "1121", "status" : "asses" }
{ "_id" : "4087322f-1ad0-4595-935f-b41ef6d87305", "lastModifiedDate" : ISODate("2016-10-22T20:48:05.904Z"), "notes" : " I do not like him that much",
"peopleId" : "1121", "status" : "asses" }
{ "_id" : "4087322f-1ad0-4595-935f-b41ef6d87304", "lastModifiedDate" : ISODate("2016-11-21T20:48:05.904Z"), "notes" : " I do not like him that much",
"peopleId" : "1121", "status" : "asses" }

and if i execute the following query 

db.mel.find().sort({lastModifiedDate:-1}).limit(1)

it gives me 

{ "_id" : "4087322f-1ad0-4595-935f-b41ef6d87306", "lastModifiedDate" : ISODate("2016-11-22T20:48:05.904Z"), "notes" : " I do not like him that much",
"peopleId" : "1121", "status" : "asses" }

which is latest as per the lastmodifeddate.

Upvotes: 0

Related Questions