mongoDB aggregate with two percent by $group

Question

My dataset :

{
    "codepostal": 84000,
    "siren": 520010234,
    "type": "home"
},
{
    "codepostal": 84000,
    "siren": 0,
    "type": "home"
},
{
    "codepostal": 84000,
    "siren": 450123003,
    "type": "appt"
} ...

My pipeline (total is an integer) :

var pipeline = [
        {
            $match: { codepostal: 84000 }
        },
        {
            $group: {
                _id: { type: "$type" },
                count: { $sum: 1 }
            }
        },
        {
            $project: {
                percentage: { $multiply: ["$count", 100 / total] }
            }
        },
        {
            $sort: { _id: 1 }
        }
    ];

Results :

[ { _id: { type: 'appt' }, percentage: 66 },
  { _id: { type: 'home' }, percentage: 34 } ]

Expected results is to count when "siren" is set to 0 or another number.

Count siren=0 => part
Count siren!=0 => pro

[ { _id: { type: 'appt' }, totalPercent: 66, proPercent: 20, partPercent: 80},
  { _id: { type: 'home' }, totalPercent: 34, proPercent: 45, partPercent: 55 } ]

Thanks a lot for your help !!

Answer 1

You can use $cond to get 0 or 1 for pro/part documents depending o value of siren field. Then it's easy to calculate totals for each type of document:

[
    {
        $match: { codepostal: 84000 }
    },
    {
        $group: {
            _id: { type: "$type" },
            count: { $sum: 1 },
            countPro: { $sum: {$cond: [{$eq:["$siren",0]}, 0, 1]} },
            countPart: {$sum: {$cond: [{$eq:["$siren",0]}, 1, 0]} }
        }
    },

    {
        $project: {
            totalPercent: { $multiply: ["$count", 100 / total] },
            proPercent: { $multiply: ["$countPro", {$divide: [100, "$count"]}] },
            partPercent: { $multiply: ["$countPart", {$divide: [100, "$count"]}] }
        }
    },

    {
        $sort: { _id: 1 }
    }
]

Note that I used $divide to calculate pro/part percentage relative to the count of document within type group.

For your sample documents (total = 3) output will be:

[
    {
        "_id" : { "type" : "appt" },
        "totalPercent" : 33.3333333333333,
        "proPercent" : 100,
        "partPercent" : 0
    },
    {
        "_id" : { "type" : "home" },
        "totalPercent" : 66.6666666666667,
        "proPercent" : 50,
        "partPercent" : 50
    }
]