0

I have a collection in my database and I have managed to write an aggregation pipeline that returns this kind of results:
ip, type, count
where ip, type are the fields I grouped by and count the number of these pairs found in my collection.
How can I now only keep the pairs ip, type where count is maximum for all ips?
e.g. 

ip1, type1, 10
ip1, type2, 20
ip1, type3, 30
ip2, type1, 30
ip2, type2, 10

From these 3, I only want to keep ip1, type3 because 30 is the max for all types of ip1 and ip2, type1 because 30 is max for all types of ip2.

Theo Stefou
  • 389
  • 2
  • 16

1 Answers1

0

You need to run another $group in order to get maxCount. All the documents belonging to a single group can be captured using a $$ROOT variable. Then you can run $filter to find the one that has highest value and use $replaceRoot to promote that doc back to root level of your result:

db.collection.aggregate([
    {
        $group: {
            _id: "$_id.ip",
            docs: { $push: "$$ROOT" },
            maxCount: { $max: "$count" }
        }
    },
    {
        $replaceRoot: {
            newRoot: {
                $arrayElemAt: [ { "$filter": { input: "$docs", cond: { $eq: [ "$$this.count", "$maxCount" ] } } } ,0]
            }
        }
    }
])

Mongo Playground

mickl
  • 48,568
  • 9
  • 60
  • 89
  • There could also be other ips in there and their counts would get in the way. I want the type for every ip where the type was found in a maximum count "row" – Theo Stefou Dec 27 '19 at 16:17
  • @TheoStefou - I see, gimme a sec and please edit your question in the meantime – mickl Dec 27 '19 at 16:19
  • 1
    Thank you for the answer, give me some time to reflect on this, I am new to mongodb. – Theo Stefou Dec 27 '19 at 16:31
  • Alright, this actually works, thank you. Any ideas on how this could be changed to get the n greatest types instead of just the first one? – Theo Stefou Dec 27 '19 at 16:49
  • @TheoStefou take a look here: https://stackoverflow.com/a/41527265/6238977 – mickl Dec 27 '19 at 19:04