CODEWITHSUNDEEP
mongodbaggregation-framework
Vikash
Vikash

Reputation: 89

Mongodb Group by get $max and count of max in value and percent of that group

I need to a group by on x field and get the max value of other fields. Yes, using $max we can get max repetitive value. But, I also need to get count $max value in percent/count too. In other words, how many times this $max value exist in that group. Kindly help.

example: 

db.getCollection("test").aggregate(
    [
        { "$match" : { "doc_id" : 1.0 } }, 
        { "$group" : {
                "_id" : { "name" : "$name" }, 
                "total" : { "$sum" : "$amount" }, 
                "l1_max" : { "$max" : "$l1" }
            }
        }, 
    ]
);

Here, I am getting l1_max = 'Computer' . But, I need it as 'Computer - (30%) Total 4/12'

Updated: 20/10/2019 @mickl : Thanks for the answer.

The field l1 is actually a referenced field. In normal find/project or mongoose populate(), it helps to get fields from other collection. Example: if l1 is of type ObjectId then, 

l1: {
_id, "4343434343sdsdsY",
name: "IT"
}

So l1.name will fetch name field from another collection in project/populate function.

I executed following code: 

db.getCollection("test").aggregate(
    [
        { "$match" : { "doc_id" : 1.0 } }, 
        { "$group" : {
                "_id" : { "name" : "$name" }, 
                "total" : { "$sum" : "$amount" }, 
                "count": { '$sum': 1 },
                "l1_max" : { "$max" : "$l1" },
                "l1_values": { $push: "$l1" }
            }
        },
        {
            $project: {
                _id: 1,
                total: 1,
                l1 : {"_id": "$l1_max", "count": "$count", "percent": { $divide: [ { $size: { $filter: { input: "$l1_values", cond: { $eq: [ "$$this", "$l1_max" ] } } } },"$count"]}}
            }
        }
    ]
);

Answer is like below: But I also need referenced name field too. 

{ 
    "_id" : {
        "name" : "xzy"
    }, 
    "total" : 35.0, 
    "l1" : {
        "_id" : "4343920239201W", 
        "name" : "IT",                // **MISSING**
        "count" : 4.0, 
        "percent" : 0.25
    }
}

Hope I was clear this time.

Upvotes: 1

Views: 363

Answers (1)

mickl
mickl

Reputation: 49975

You need to capture all l1 values in your group and the calculate the percent using $divide, $filter and $size:

db.getCollection("test").aggregate(
    [
        { "$match" : { "doc_id" : 1.0 } }, 
        { "$group" : {
                "_id" : { "name" : "$name" }, 
                "total" : { "$sum" : "$amount" }, 
                "l1_max" : { "$max" : "$l1" },
                "l1_values": { $push: "$l1" }
            }
        },
        {
            $project: {
                _id: 1,
                total: 1,
                l1_max: 1,
                l1_perc: { 
                    $divide: [
                        { $size: { $filter: { input: "$l1_values", cond: { $eq: [ "$$this", "$l1_max" ] } } } },
                        { $size: "$l1_values" }
                    ]
                }
            }
        }
    ]
);

Mongo Playground

Upvotes: 1

Related Questions