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)

What have you tried? This seems a most common and basic use case of $group — ray
– ray, Commented Feb 6, 2023 at 19:12
That's much better now. You can check if any of the below answers work for you. Consider accepting and upvoting if an answer works for you. This helps to avoid duplicate efforts from the community to check the issue again. — ray
– ray, Commented Feb 7, 2023 at 17:41

Prashant Pokhriyal · Accepted Answer · 2023-02-06 19:24:40Z

1

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" 
        } 
    }
])

answered Feb 6, 2023 at 19:24

Prashant Pokhriyal

3,8474 gold badges33 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

avukalov Over a year ago

Thank you for your answer. I edited my question. This will be second $unwind if I choose this approach. Should I write 2 queries?

Santi Trayko · Accepted Answer · 2023-02-06 19:51:57Z

0

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
     }
    }]

answered Feb 6, 2023 at 19:51

Santi Trayko

1044 bronze badges

4 Comments

avukalov Over a year ago

Thank you for your answer. I edited my question. What would be better solution? I'm open for suggestions. Honestly, I was thinking about something like your solution but it not seems natural for mongo so I asked for help...

avukalov Over a year ago

I adjusted my query to get exact same data as I described in question ($member.rosterUnit is an array of strings). In $group stage, I got error , "$in requires an array as a second argument, found: missing". If I cut off entire $group stage I got $member.rosterUnit is an array.. What is going on?

Santi Trayko Over a year ago

In that case the only thing I can think of is that maybe you have some "$membrt.rosterUnit" with null value. Try adding a $match stage before the $group that filters only the array type using the $isArray operator.

Santi Trayko Over a year ago

I think that the best solution it's te first answer. The one that use the unwind

Takis · Accepted Answer · 2024-01-05 19:42:59Z

0

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"}}}}}])

edited Jan 5, 2024 at 19:42

answered Feb 6, 2023 at 20:42

Takis

8,7552 gold badges17 silver badges28 bronze badges

2 Comments

avukalov Over a year ago

Thank you for your answer. I edited my question with more details. This is a similar solution to previous but your includes $unwind. Is it possible to get result without $unwind?

Takis Over a year ago

there is a trick to do the unwind in local way, using $lookup with a collection with 1 document, but if you dont want to use unwind you can the previous answer, that checks the arrays(if no duplicates exists i think it will work fine), and maybe take from this answer the last project.

Collectives™ on Stack Overflow

Count of equal elements in array across multiple documents in mongodb

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related