CODEWITHSUNDEEP
databasemongodbmaxaggregation
Thomas Gak-Deluen
Thomas Gak-Deluen

Reputation: 2971

Mongodb: Get top documents

I am unable to find an answer to a problem either on Google or SO.

I have a collection such as :

{ "_id" : "0", "timestamp" : 160000 }
{ "_id" : "00", "timestamp" : 160000 }
{ "_id" : "000", "timestamp" : 150000 }

And I want to get the top rows based on timestamp, not only the top one.

This for example:

{ "_id" : "0", "timestamp" : 160000 }
{ "_id" : "00", "timestamp" : 160000 }

The obvious solution would be to sort DESC and get the first n rows, but this doesn't actually do what is required, I would need to know the number of top elements etc.

I'd like to get the timestamp of the top row and then match all rows that have that timestamp, or perhaps something else?

Thank you in advance!

Upvotes: 0

Views: 213

Answers (1)

hhharsha36
hhharsha36

Reputation: 3349

You have to make use of self lookup to perform lookup on the same collection after finding the max value.

db.collection.aggregate([
  {
    "$sort": {
      "timestamp": -1
    }
  },
  {
    "$limit": 1
  },
  {
    "$lookup": {
      "from": "collection",
      "localField": "timestamp",
      "foreignField": "timestamp",
      "as": "topTimeStamp"
    }
  },
  {
    "$project": {
      "_id": 0,
      "result": "$topTimeStamp"
    }
  },
  
])

Mongo Sample Execution

Sort timestamp key in descending order to improve query performance.

If the number of documents in the collection will be lesser, I recommend you to replace $sort and $limit stages with $group stage and find the max value using $max accumulator.

Upvotes: 2

Related Questions