How to count number of objects by a selected attribute(in nested) using Mongdb

Question

I have a collection that has documents taking a structure like this.

{
    "_id" : ObjectId("5d6db92e8e935c407f00f39c"),
    "id" : "1",
    "email" : "admin@test.com",
    "orgs" : [ 
        {
            "org_id" : "1",
            "org_name" : "Lenovo",
            "role" : "tenantadmin",
            "primary_locale" : null,
            "name" : "admin"
        }
    ]
}

I need to get the count of admin roles available and also the count of other roles(any other tenantadmin, admin, user). So that it would give a result like

{admin:10, others:20}

This is the code that I have tried out.

db.getCollection('users').aggregate([{'$unwind': '$orgs'},{ '$group': { '_id': "$orgs.role",'count': {'$sum': 1}}}])

Which gives me a count of all the type of roles

{
    "_id" : "user",
    "count" : 3.0
}
{
    "_id" : "tenantadmin",
    "count" : 2.0
}
{
    "_id" : "admin",
    "count" : 5.0
}

How to get an output like this {admin:10, others:20} ?.

Answer 1

Try as below:

db.collection.aggregate([
        {
            "$addFields": {
                "other": {
                    "$size": { 
                        "$filter": {
                            "input": "$orgs",
                            "as": "el",
                            "cond": { "$ne": [ "$$el.role", "admin" ] }
                        }
                    }
                },
                "admin": {
                    "$size": { 
                        "$filter": {
                            "input": "$orgs",
                            "as": "el",
                            "cond": { "$eq": [ "$$el.role", "admin" ] }
                        }
                    }
                }
            }
        },
        {
            $project: {
                admin:1,
                other:1
            }
        }


    ])

Result will be :

{
    "_id" : ObjectId("5de0b60ec6794c1b2be95902"),
    "other" : 2,
    "admin" : 1
}

Answer 2

You can use $cond to define your grouping key:

db.getCollection('users').aggregate([
    { '$unwind': '$orgs' },
    { '$group': { '_id': { $cond: [ { $eq: [ "$orgs.role", "admin" ] }, "$orgs.role", "other" ] },'count': {'$sum': 1}}}
    ]
)

Mongo Playground

EDIT: to get your grouping _id as result's keys you can run another $group followed by $replaceRoot with $arrayToObject:

db.getCollection('users').aggregate([
    { '$unwind': '$orgs' },
    { '$group': { '_id': { $cond: [ { $eq: [ "$orgs.role", "admin" ] }, "$orgs.role", "other" ] },'count': {'$sum': 1}}},
    { '$group': { '_id': null, root: { $push: { k: '$_id', v: '$count' } } } },
    { '$replaceRoot': { newRoot: { $arrayToObject: '$root' } } }
    ]
)

Mongo Playground