Count of equal elements in array across multiple documents in mongodb

Question

I'm working on simple program that counts total number of special units through n number of players.

I have documents similar to this (simplified), where array rosterUnits could be of length 0 to 7. There is a total of 7 special units. I need to know how many of each unit players have in roster.

{
  {
    _id: ObjectId(...),
    member: {
      rosterUnits: [ "Unit1", "Unit2", "Unit3", "Unit4"]
    }
  },
  {
    _id: ObjectId(...),
    member: {
      rosterUnits: [ "Unit1", "Unit3"]
    }
  },
  ...
}

Expected result would be something like this:

{
  _id: ...
  result: [
    {
      name: "Unit1"
      count: 2
    },
    {
      name: "Unit2"
      count: 1
    },
    {
      name: "Unit3"
      count: 2
    },
    ...
    {
      name: "Unit7"
      count: 0
    }
  ]
}

How do I achieve this using aggregate pipeline?

EDIT (2/7/2023)

Excuse me everyone, I thought I provided enough details here but... Documents are very big and pipeline until this stage is very long. I wanted to spare you the trouble with the documents

I have guild with up to 50 players. I search for guild then $unwind members of guild and $lookup into members to get member.rosterUnit(s).

This is a full query I came up with:

db.getCollection('guilds').aggregate([
    { $match: { 'profile.id': 'jrl9Q-_CRDGdMyNjTQH1rQ' } },
    //{ $match: { 'profile.id': { $in : ['jrl9Q-_CRDGdMyNjTQH1rQ', 'Tv_j9nhRTgufvH7C7oUYAA']} } },
    { $project: { member: 1, profile: 1 } },
    { $unwind: "$member" },
    {
        $lookup: {
            from: "players",
            localField: "member.playerId",
            foreignField: "playerId",
            pipeline: [
                {
                    $project: {
                        profileStat: 1,
                        rosterUnit: {
                            $let: {
                                vars: { gls: ["JABBATHEHUTT:SEVEN_STAR", "JEDIMASTERKENOBI:SEVEN_STAR", "GRANDMASTERLUKE:SEVEN_STAR", "LORDVADER:SEVEN_STAR", "GLREY:SEVEN_STAR", "SITHPALPATINE:SEVEN_STAR", "SUPREMELEADERKYLOREN:SEVEN_STAR"], },
                                in: {
                                    $reduce: {
                                        input: "$rosterUnit",
                                        initialValue: [],
                                        in: {
                                            $cond: {
                                                if: { $gt: [{ $indexOfArray: ["$$gls", "$$this.definitionId"] }, -1] },
                                                then: { $concatArrays: ["$$value", [{ definitionId: "$$this.definitionId", count: 1 }]] },
                                                else: { $concatArrays: ["$$value", []] }
                                            }
                                        },
                                    }
                                }
                            }
                        }
                    }
                }
            ],
            as: "member"
        }
    },
    {
        $addFields: {
            member: { $arrayElemAt: ["$member", 0] },
            gpStats: {
                $let: {
                    vars: { member: { $arrayElemAt: ["$member", 0] } },
                    in: {
                        $reduce: {
                            input: "$$member.profileStat",
                            initialValue: {},
                            in: {
                                characterGp: {
                                    $arrayElemAt: [
                                        "$$member.profileStat.value",
                                        {
                                            $indexOfArray: [
                                                "$$member.profileStat.nameKey",
                                                "STAT_CHARACTER_GALACTIC_POWER_ACQUIRED_NAME"
                                            ]
                                        }
                                    ]
                                },
                                shipGp: {
                                    $arrayElemAt: [
                                        "$$member.profileStat.value",
                                        {
                                            $indexOfArray: [
                                                "$$member.profileStat.nameKey",
                                                "STAT_SHIP_GALACTIC_POWER_ACQUIRED_NAME"
                                            ]
                                        }
                                    ]
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    {
        $group: {
            _id: "$profile.id",
            guildName: { $first: "$profile.name" },
            memberCount: { $first: "$profile.memberCount" },
            guildGp: { $first: "$profile.guildGalacticPower" },
            totalGp: { $sum: { $sum: [{ $toInt: "$gpStats.characterGp" }, { $toInt: "$gpStats.shipGp" }] } },
            avgTotalGp: { $avg: { $sum: [{ $toInt: "$gpStats.characterGp" }, { $toInt: "$gpStats.shipGp" }] } },
            characterGp: { $sum: { $toInt: "$gpStats.characterGp" } },
            shipGp: { $sum: { $toInt: "$gpStats.shipGp" } },

        }
    }

])

I want to add new field in group with desired result from above. If I do $unwind on member.rosterUnit how do I go back to member grouping?

(Excuse me once again, this is my first question)

Answer 1

Query

• because you want to count values that might not exists, you can make the groups manualy, and do conditional count
• after the group you can do extra tranformation(if you really need the expected outpute exactly like that). Object to array, and map to give the field names(name,count)

Playmongo

aggregate(
[{"$unwind": "$member.rosterUnits"},
 {"$group": 
   {"_id": null,
    "Unit1": 
     {"$sum": 
       {"$cond": [{"$eq": ["$member.rosterUnits", "Unit1"]}, 1, 0]}},
    "Unit2": 
     {"$sum": 
       {"$cond": [{"$eq": ["$member.rosterUnits", "Unit2"]}, 1, 0]}},
    "Unit3": 
     {"$sum": 
       {"$cond": [{"$eq": ["$member.rosterUnits", "Unit3"]}, 1, 0]}},
    "Unit4": 
     {"$sum": 
       {"$cond": [{"$eq": ["$member.rosterUnits", "Unit4"]}, 1, 0]}},
    "Unit5": 
     {"$sum": 
       {"$cond": [{"$eq": ["$member.rosterUnits", "Unit5"]}, 1, 0]}},
    "Unit6": 
     {"$sum": 
       {"$cond": [{"$eq": ["$member.rosterUnits", "Unit6"]}, 1, 0]}},
    "Unit7": 
     {"$sum": 
       {"$cond": [{"$eq": ["$member.rosterUnits", "Unit7"]}, 1, 0]}}}},
 {"$unset": ["_id"]},
 {"$project": 
   {"result": 
     {"$map": 
       {"input": {"$objectToArray": "$$ROOT"},
        "in": {"name": "$$this.k", "count": "$$this.v"}}}}}])

Answer 2

Yes I think that the best way of do that is using aggregations.

I'm sure there is a better way to do it. But here is the solution, I hope it works for you friend.

Basically we are going to use a "$group" aggregation and within it using an operator "$cond" and "$in" we are going to validate case by case if the searched element is found. In the case that it is so, we will add one and if the element is not found, zero.

I recommend you download mongodb compass to try it

Aggregation:

    [{
     $group: {
      _id: null,
      Unit1: {
       $sum: {
        $cond: [
         {
          $in: [
           'Unit1',
           '$member.rosterUnits'
          ]
         },
         1,
         0
        ]
       }
      },
      Unit2: {
       $sum: {
        $cond: [
         {
          $in: [
           'Unit2',
           '$member.rosterUnits'
          ]
         },
         1,
         0
        ]
       }
      },
      Unit3: {
       $sum: {
        $cond: [
         {
          $in: [
           'Unit3',
           '$member.rosterUnits'
          ]
         },
         1,
         0
        ]
       }
      },
      Unit4: {
       $sum: {
        $cond: [
         {
          $in: [
           'Unit4',
           '$member.rosterUnits'
          ]
         },
         1,
         0
        ]
       }
      },
      Unit5: {
       $sum: {
        $cond: [
         {
          $in: [
           'Unit5',
           '$member.rosterUnits'
          ]
         },
         1,
         0
        ]
       }
      },
      Unit6: {
       $sum: {
        $cond: [
         {
          $in: [
           'Unit6',
           '$member.rosterUnits'
          ]
         },
         1,
         0
        ]
       }
      },
      Unit7: {
       $sum: {
        $cond: [
         {
          $in: [
           'Unit7',
           '$member.rosterUnits'
          ]
         },
         1,
         0
        ]
       }
      }
     }
    }, {
     $project: {
      _id: 0
     }
    }]

Answer 3

• Use $unwind to deconstruct the rosterUnits array into separate documents.
• Then use $group to group the documents by the rosterUnits values and calculate the count for each unit.
• Then use $project to format the output to include only the name and count fields.

db.collection.aggregate([
    { 
        $unwind: "$member.rosterUnits" 
    },
    { 
        $group: { 
            _id: "$member.rosterUnits", 
            count: { $sum: 1 } 
        } 
    },
    { 
        $project: { 
            _id: 0, 
            name: "$_id", 
            count: "$count" 
        } 
    }
])