CODEWITHSUNDEEP
mongodb
Vijay Vavdiya
Vijay Vavdiya

Reputation: 1309

How to make two sequential query into single query in MongoDB

I MongoDB collection, I am storing multiples records by one batch ID. I have kept batch Id as milliseconds of Date() object. How to retrieve all records which have recent/max batch ID in single MongoDB query.

Right now, I am retrieving records in two sequential query:

  1. Find a max/recent batch id - recentBatchID = db.users.find().sort( { 'batchID': -1 }).limit(1)

  2. Get records by max/recent batch ID db.users.find({'batchID':recentBatchID})

How we can optimize in single Mongo query?

Sample Data:

[{
        "username": "user1",
        "batchID": "00001"
    },
    {
        "username": "user2",
        "batchID": "00001"
    },
    {
        "username": "user3",
        "batchID": "00001"
    },
    {
        "username": "user4",
        "batchID": "00002"
    },
    {
        "username": "user5",
        "batchID": "00002"
    },
    {
        "username": "user6",
        "batchID": "00002"
    },
]

I am trying to get only user4, user5, user6 which have max/recent batchID-00002.

Upvotes: 0

Views: 305

Answers (1)

Hardik Shah
Hardik Shah

Reputation: 4200

As per new update, you can use aggregation then:

db.getCollection('tests').aggregate([
  {$group: {
     _id : "$batchID",
     doc : {$push : "$$ROOT"},
   }},
   {$sort : { '_id': -1 }},
   {$limit: 1},
   {$unwind: "$doc"}
])

Output:

/* 1 */
{
    "_id" : "00002",
    "doc" : {
        "_id" : ObjectId("5bd2dcced79cc5d8b1c6296e"),
        "username" : "user4",
        "batchID" : "00002"
    }
}

/* 2 */
{
    "_id" : "00002",
    "doc" : {
        "_id" : ObjectId("5bd2dcd3d79cc5d8b1c6296f"),
        "username" : "user5",
        "batchID" : "00002"
    }
}

/* 3 */
{
    "_id" : "00002",
    "doc" : {
        "_id" : ObjectId("5bd2dcdbd79cc5d8b1c62970"),
        "username" : "user6",
        "batchID" : "00002"
    }
}

Upvotes: 1

Related Questions