Mongodb aggregation limit occurrence of individual field value-CodePudding

I have a list of blog posts with category and timestamp. Now if I'd like to order them chronologically but limit the occurrence of each category to, say, only 3 posts per category, a regular $group doesn't seem like the right approach. Same goes for topN since that groups those posts per category but I'm losing the chronological list.

CodePudding user response：

You can still use "$group" with "$topN" and then, for example, follow that with another "$group", sorting, "$unwind", and "$replaceWith".

N.B.: This will give the earliest posts. If you want the latest posts, you can adjust the sort order.

db.blogPosts.aggregate([
  { // get max 3 items from category
    // sorted by timestamp
    "$group": {
      "_id": "$category",
      "postsTop3": {
        "$topN": {
          "n": 3,
          "sortBy": {"timestamp": 1},
          "output": "$$ROOT"
        }
      }
    }
  },
  { // aggregate all top3's
    "$group": {
      "_id": null,
      "posts": {"$push": "$postsTop3"}
    }
  },
  { // sort them all by timestamp
    "$set": {
      "posts": {
        "$sortArray": {
          "input": {
            "$reduce": {
              "input": "$posts",
              "initialValue": [],
              "in": {
                "$concatArrays": ["$$value", "$$this"]
              }
            }
          },
          "sortBy": {"timestamp": 1}
        }
      }
    }
  },
  {"$unwind": "$posts"},
  {"$replaceWith": "$posts"}
])

Try it mongoplayground.net.