英文:
Count of equal elements in array across multiple documents in mongodb
问题
I'm working on a simple program that counts the total number of special units for each player in a group of players.
I have documents similar to this (simplified), where the rosterUnits
array can have a length from 0 to 7. There is a total of 7 special units, and I need to know how many of each unit players have in their rosters.
Here's an example of the data structure:
{
{
_id: ObjectId(...),
member: {
rosterUnits: [ "Unit1", "Unit2", "Unit3", "Unit4"]
}
},
{
_id: ObjectId(...),
member: {
rosterUnits: [ "Unit1", "Unit3" ]
}
},
// ...
}
The expected result should look like this:
{
_id: ...,
result: [
{
name: "Unit1",
count: 2
},
{
name: "Unit2",
count: 1
},
{
name: "Unit3",
count: 2
},
// ...
{
name: "Unit7",
count: 0
}
]
}
You are looking to achieve this using an aggregate pipeline.
EDIT (2/7/2023)
You have a complex query that involves multiple stages of aggregation, and you want to add a new field to the final group with the desired result from the earlier stages. If you perform $unwind
on member.rosterUnit
, you can go back to the member grouping by using $group
again to group the data based on member-related fields.
英文:
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)
答案1
得分: 1
- 使用
$unwind
将rosterUnits
数组拆分为单独的文档。 - 然后使用
$group
将文档按rosterUnits
值分组,并计算每个单位的数量。 - 最后使用
$project
格式化输出,只包括名称和计数字段。
db.collection.aggregate([
{
$unwind: "$member.rosterUnits"
},
{
$group: {
_id: "$member.rosterUnits",
count: { $sum: 1 }
}
},
{
$project: {
_id: 0,
name: "$_id",
count: "$count"
}
}
])
英文:
- Use
$unwind
to deconstruct therosterUnits
array into separate documents. - Then use
$group
to group the documents by therosterUnits
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"
}
}
])
答案2
得分: 0
是的,我认为最好的方法是使用聚合操作。
我相信有更好的方法来做这件事。
但这里是解决方案,希望它对你有用。
基本上,我们将使用“$group”聚合,其中使用运算符“$cond”和“$in”来逐个验证搜索的元素是否被找到。如果找到了,我们将添加1,如果未找到元素,将添加0。
我建议你下载mongodb compass来尝试它
聚合:
[{
$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
}
}]
英文:
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
}
}]
答案3
得分: 0
以下是您要翻译的内容:
- 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)
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"}}}}}])
英文:
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)
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"}}}}}])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论