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?

Andriy Simonov · Accepted Answer · 2016-07-28T11:48:06.293

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"}}]);

Is there a way to get all fields additionally to these three? I mean an analog of $$ROOT — Lyubimov Roman, Jul 28 '16 at 10:54
Now I understand that don't know how to even one another field :) — Lyubimov Roman, Jul 28 '16 at 11:12
Thanks a lot! Really. If you don't mind one more question. Is there correct way to get all fields as $ROOT does but without parent object? Some kind of sreading. — Lyubimov Roman, Jul 28 '16 at 14:14

score 0 · Answer 2 · answered Jul 27 '16 at 18:30

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.

Thanks for answering! There is one more answer above, and your answer have good description, but contain more code, could you please comment it? Is your solution better then it? — Lyubimov Roman, Jul 28 '16 at 09:59
It's good to go with **Andriy** solution because of my aggregation also works but not performance using of lot more aggregation steps — Neo-coder, Jul 28 '16 at 10:36
I have trouble with adding other fields with Andriy's solution, I guess with your solution It can be done but not with his solution, am I right? — Lyubimov Roman, Jul 28 '16 at 11:31
@FriOne if you have other fields then used `$$ROOT` in aggregation and go with my solution and this aggregation last `unwind` and `projection` was optional you will get the data in `findMax` array . — Neo-coder, Jul 28 '16 at 11:58

Get all rows, groupped and with max value

2 Answers2

Linked