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