Get all rows, groupped and with max value

Question

For example, I have this data:

{project: "1": platform: "1", number: 10}
{project: "1": platform: "1", number: 10}
{project: "1": platform: "1", number: 40}
{project: "1": platform: "1", number: 40}

{project: "1": platform: "2", number: 20}
{project: "1": platform: "2", number: 20}
{project: "1": platform: "2", number: 30}
{project: "1": platform: "2", number: 30}

{project: "2": platform: "2", number: 50}
{project: "2": platform: "2", number: 50}
{project: "2": platform: "2", number: 60}
{project: "2": platform: "2", number: 60}

I want to get rows groupped by project and platform, and get all rows, that have max number. Result from the data above should be:

{project: "1": platform: "1", number: 40}
{project: "1": platform: "1", number: 40}
{project: "1": platform: "2", number: 30}
{project: "1": platform: "2", number: 30}
{project: "2": platform: "2", number: 60}
{project: "2": platform: "2", number: 60}

I've tried to make an aggregation with project and platform inside $group._id with $max, but query returns only one row with max number. How it can be made by mongodb?

Answer 1

The following is a solution based on $filter operator:

db.projects.aggregate([{$group: {_id: {project: "$project", platform: "$platform"}, numbers: {$push: "$number"}, max: {$max: "$number"}}},
                       {$project: {_id: 0,
                                   project: "$_id.project",
                                   platform: "$_id.platform",
                                   number: {$filter: {
                                                      input: "$numbers",
                                                      as: "number",
                                                      cond: {$eq: ["$$number", "$max"]}
                                                     }
                                           }
                                  }
                       },
                       {$unwind: "$number"}]);

In case you have more fields in the original document you can try this:

As far as I understand you need something like this db.projects.aggregate([{$group: {_id: {project: "$project", platform: "$platform"}, documents: {$push: "$$ROOT"}, max: {$max: "$number"}}},
                       {$project: {_id: 0,
                                   document: {$filter: {
                                                      input: "$documents",
                                                      as: "document",
                                                      cond: {$eq: ["$$document.number", "$max"]}
                                                     }
                                           }
                                  }
                       },
                       {$unwind: "$document"},
                       {$project: {_id: "$document._id", number: "$document.number", ANOTHER_FIELD: "$ANOTHER_FIELD"}}]);

Answer 2

before going to aggregation query check below links for reference :

1> Mongo $eq

2> $setDifference

3> $sond

4> $map

first we grouped by platform and project and find max number using group in aggregation and add all data using $$ROOT in group. Then using $map iterate over all data and check with max number matched and get only those max number matched data.

db.collection.aggregate([{
    "$group": {
        "_id": {
            "project": "$project",
            "paltform": "$platform"
        },
        "max": {
            "$max": "$number"
        },
        "mainData": {
            "$push": "$$ROOT"
        }
    }
}, {
    "$project": {
        "findMax": {
            "$setDifference": [{
                    "$map": {
                        "input": "$mainData",
                        "as": "el",
                        "in": {
                            "$cond": {
                                "if": {
                                    "$eq": ["$$el.number", "$max"]
                                },
                                "then": "$$el",
                                "else": false
                            }
                        }
                    }
                },
                [false]
            ]
        },
        "_id": 0
    }
}, {
    "$unwind": "$findMax"
}, {
    "$project": {
        "_id": "$findMax._id",
        "project": "$findMax.project",
        "platform": "$findMax.platform",
        "number": "$findMax.number"
    }
}])

also if you removed last uniwnd and project you also get your results.