在MongoDB中查找重复项，同时比较这些重复项中的某些字段

Question

我有一个数据库

[
{
"_id": 1,
"email": "[e-mail]",
"status": "ACTIVE"
},
{
"_id": 2,
"email": "[e-mail]",
"status": "INACTIVE"
},
{
"_id": 3,
"email": "[e-mail]",
"status": "ACTIVE"
},
{
"_id": 4,
"email": "[e-mail]",
"status": "INACTIVE"
},
{
"_id": 5,
"email": "[e-mail]",
"status": "ACTIVE"
},
{
"_id": 6,
"email": "[e-mail]",
"status": "ACTIVE"
},

]

现在我想根据电子邮件找到该项目，其状态为ACTIVE和INACTIVE。我编写查询是为了查找类似的重复项。

db.getCollection(‘employees’).aggregate([
{$group: {
_id: {email: “$email”},
uniqueIds: {$addToSet: “$_id”},
count: {$sum: 1}
}
},
{$match: {
count: {“$gt”: 1}
}
}
], {allowDiskUse:true });

此返回两者[e-mail]和[e-mail]但我只想[e-mail]因为它在db中同时为ACTIVE和INACTIVE。结果应该看起来像

{
"_id": {
"email": "[e-mail]"
},
"uniqueIds": [
4,
1
]
}

Answer 1

尝试以下查询。

db.getCollection("employees").aggregate([
{
$group: {
_id: {
email: "$email"
},
uniqueIds: {
$addToSet: "$_id"
},
status: {
$addToSet: "$status"
}
}
},
{
$match: {
status: {
"$all": [
"ACTIVE",
"INACTIVE"
]
}
}
},
{
$project: {
status: 0
}
}
])

这里是MongoPlayground。