Get percentages with MongoDB aggregate $group

Question

I'd like to get percentages from a group pipeline in a MongoDB aggregate.

My data:

{
    _id : 1,
    name : 'hello',
    type : 'big'
},
{
    _id : 2,
    name : 'bonjour',
    type : 'big'
},
{
    _id : 3,
    name : 'hi',
    type : 'short'
},
{
    _id : 4,
    name : 'salut',
    type : 'short'
},
{
    _id : 5,
    name : 'ola',
    type : 'short'
}

My request group by type, and count:

[{
    $group : {
        _id : {
            type : '$type'
        },
        "count" : {
            "$sum" : 1
        }
    }
}]

Result:

[
    {
        _id {
            type : 'big',
        },
        count : 2
    },
    {
        _id {
            type : 'short',
        },
        count : 3
    }
]

But I'd like to have count AND percentage, like that:

[
    {
        _id {
            type : 'big',
        },
        count: 2,
        percentage: 40%
    },
    {
        _id {
            type : 'short',
        },
        count: 3,
        percentage: 60%
    }
]

But I've no idea how to do that. I've tried $divide and other things, but without success. Could you please help me?

Solution 1

Well I think percentage should be string if the value contains %

First get you will need to count the number of document.

var nums = db.collection.count();

db.collection.aggregate(
    [
        { "$group": { "_id": {"type":  "$type"}, "count": { "$sum": 1 }}},    
        { "$project": { 
            "count": 1, 
            "percentage": { 
                "$concat": [ { "$substr": [ { "$multiply": [ { "$divide": [ "$count", {"$literal": nums }] }, 100 ] }, 0,2 ] }, "", "%" ]}
            }
        }
    ]
)

Result

{ "_id" : { "type" : "short" }, "count" : 3, "percentage" : "60%" }
{ "_id" : { "type" : "big" }, "count" : 2, "percentage" : "40%" }

Solution 2

First find total number of documents in collections using count method and used that count variable to calculate percentage in aggregation like this :

var totalDocument = db.collectionName.count() //count total doc.

used totalDocument in aggregation as below :

db.collectionName.aggregate({"$group":{"_id":{"type":"$type"},"count":{"$sum":1}}},
                            {"$project":{"count":1,"percentage":{"$multiply":[{"$divide":[100,totalDocument]},"$count"]}}})

EDIT

If you need to this in single aggregation query then unwind used in aggregation but using unwind it creates Cartesian problem check below aggregation query :

db.collectionName.aggregate({"$group":{"_id":null,"count":{"$sum":1},"data":{"$push":"$$ROOT"}}},
                            {"$unwind":"$data"},
                             {"$group":{"_id":{"type":"$data.type"},"count":{"$sum":1},
                                       "total":{"$first":"$count"}}},
                             {"$project":{"count":1,"percentage":{"$multiply":[{"$divide":[100,"$total"]},"$count"]}}}
                            ).pretty()

I recconmed first find out toatal count and used that count in aggregation as per first query.

Comments

[user3582562]

Thanks. Indeed, it sounds like it's not possible to have the result with just one aggregate.

[Yogesh]

yes is possible in one aggregation check EDIT but problem with in one aggregation it unwind data and create a multiple documents.

[Tony Mathew]

how do I round of the percentage value to two decimals? In your solution, if the 'percentage' value is 16.66, it will return only '16%', what edits should I make in your solution so that it returns '16.70%' ?

[styvane]

@TonyMathew to round the percentage look at $round new in 4.2 or see this answer

[newdeveloper]

could you please on this one too ? stackoverflow.com/questions/61145727/…