Limit with ties in MongoDB-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

Query

uses $setWindowFields, requires MongoDB >= 5
sort by movies (it has the movie count)
$denseRank will rank the members but same movie count will get the same rank
we filter max rank 3 (if duplicates it might be more like in the example bellow)

Test code here

db.collection.aggregate([
  {
    "$setWindowFields": {
      "output": {
        "dense-rank": {
          "$denseRank": {}
        }
      },
      "sortBy": {
        "movies": -1
      }
    }
  },
  {
    "$match": {
      "$expr": {
        "$lte": [
          "$dense-rank",
          3
        ]
      }
    }
  },
  {
    "$unset": [
      "dense-rank"
    ]
  }
])