Count per value in existing grouping

Working with Data
1

Hey all. I am trying to write an aggregation for statistics of Clan entities, where each stat is grouped by Region and Faction of that Clan entity. Additionally, each clan has a level, so I want to count how many clans in each region+faction grouping have each level.
So far I build an aggregation like this (slightly simplified for brevity):

db.Clans.aggregate(
{
    "$project": {
        "faction": 1,
        "region": 1,
        "memberCount": 1,
        "level": 1
    }
},
{
    "$group": { 
        "_id": {
            "faction": "$faction", 
            "region": "$region"
        },
        "clanCount": { "$sum": 1 },
        "memberCount": { "$sum": "$memberCount" },
        "averageLevel": { "$avg": "$level" },
        "averageMemberCount": { "$avg": "$memberCount" },
        "level1Clans": {
            "$sum": {
                "$switch": {
                    "branches": [
                        { "case": { "$eq": [ "$level", 1 ] }, "then": 1 }
                    ],
                    "default": 0
                }
            }
        },
        "level2Clans": {
            "$sum": {
                "$switch": {
                    "branches": [
                        { "case": { "$eq": [ "$level", 2 ] }, "then": 1 }
                    ],
                    "default": 0
                }
            }
        },
        "level3Clans": {
            "$sum": {
                "$switch": {
                    "branches": [
                        { "case": { "$eq": [ "$level", 3 ] }, "then": 1 }
                    ],
                    "default": 0
                }
            }
        }
     }
},
{
    "$project": {
        "faction": "$_id.faction",
        "region": "$_id.region",
        "clanCount": "$clanCount",
        "memberCount": "$memberCount",
        "level": "$averageLevel",
        "avgMemberCount": "$averageMemberCount",
        "clansPerLevel": {
            "1": "$level1Clans",
            "2": "$level2Clans",
            "3": "$level3Clans",
        }
    }
}
)

This aggregation works and gives expected data, but it is pretty verbose - and will become even more verbose if a clan can have more than 3 levels (currently it can’t, but it might in future).

I was wondering if it can be made better. I tried googling and trying out stuff like $map and what not, but nothing worked so far - the aggregation I posted above is so far the only one that worked.

2

One idea that comes to mind is that you can try to do a first $group with

"_id" : { "faction" : "$faction" , "region" : "$region" , "level" : "$level" } ,
"levelClans" : { "$sum" : 1 }

The a second $group with

"_id" : { "faction" : "$_id.faction" , "region" : "$_id.region" } ,
"clansPerLevel" : {
    "$push" : { "level" : "$_id.level" , "clans" : "$levelClans" }
}

You might need to change where and how you get your $avg.

3

Sorry for late reply, got distracted by other things.

Thank you, this works! As you mentioned, I had to move average to a separate query and then merge the results in app memory, but it’s a query that runs 2-3 times a hour so it’s not a huge problem.

1 Like