Get maximum value from MongoDB aggregation query-CodePudding

I have a list of posts:

[{"name: "POST 1", {"name": "POST 2",}, {"name": "POST 1",}

I want to count the number of identical names and output the maximum number. The expected result is: {"id: "POST 1", count:2}

Now I wrote this query

db.Posts.aggregate([ 
    {$group: { _id: "$post.name", count: { $sum: 1 } }
])

The output I get is: [{"id: "POST 1", count:2}, {"id: "POST 1", count:1}]. How can I now get the maximum from this list? Without using sort and limit, because there may be several identical min.

CodePudding user response：

One more option (a variation on @rickhg12hs' s answer) is using $setWindowFields, since $facet requires to group all your documents into one large document, and a document have a size limit:

db.posts.aggregate([
   {$match: {date_created: {
        $gte: new Date("2022-10-01"),
        $lte: new Date("2022-10-30")
   }}},
  {$sortByCount: "$name"},
  {$setWindowFields: {sortBy: {count: -1}, output: {maxCount: {$max: "$count"}}}},
  {$match: {$expr: {$eq: ["$count", "$maxCount"]}}},
  {$unset: "maxCount"}
])

See how it works on the playground example

CodePudding user response：

Here's one way to get all the most frequent "name" values with their count.

db.posts.aggregate([
  {"$sortByCount": "$name"},
  {
    "$facet": {
      "maxCount": [
        {"$limit": 1},
        {
          "$project": {
            "_id": 0,
            "count": 1
          }
        }
      ],
      "postsWithCount": [
        {
          "$project": {
            "_id": 0,
            "name": "$_id",
            "count": 1
          }
        }
      ]
    }
  },
  {
    "$project": {
      "postsWithMaxCount": {
        "$filter": {
          "input": "$postsWithCount",
          "cond": {"$eq": ["$$this.count", {"$first": "$maxCount.count"}]}
        }
      }
    }
  },
  {"$unwind": "$postsWithMaxCount"},
  {"$replaceWith": "$postsWithMaxCount"}
])

Try it on mongoplayground.net.