Search code examples
mongodbaggregation-frameworklimit

Limit with ties in MongoDB

I have a database with movie data: title and year. I need a query to perform a count of movies by year and return the year with the highest count. My struggle is that I need it to be dynamic, so if there is a tie, it should return all the "tied" years.

Currently my query looks like this:

db.movies.aggregate({$group : {_id : "$year", movies: {$sum : 1}}}, {$sort : {"movies" : -1}}, {$limit : 3})

Right now I have a tie between three movies, hence the limit 3 but I would like it to be dynamic. Any help would be appreciated! Thanks in advance!

Solution

  • use double $group, the second one group by count.

    db.collection.aggregate({
  $group: {
    _id: "$year",
    movies: {
      $sum: 1
    }
  }
},
{
  "$group": {
    "_id": "$movies",
    "yearList": {
      "$push": "$$ROOT._id"
    }
  }
},
{
  $sort: {
    "_id": -1
  }
},
{
  "$limit": 1
})

    mongoplayground