Find Duplicate In MongoDB While Comparing Some Field In those Duplicate Items

Question

I Have a DB

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

Now I want to find the item according to emails, which have status as both ACTIVE and INACTIVE. I have written the query to find duplicates like this.

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

This return both [email protected] and [email protected] but I only want [email protected] as it as both ACTIVE and INACTIVE in db. Result should look like

{
    "_id": {
      "email": "[email protected]"
    },
    "uniqueIds": [
      4,
      1
    ]
  }

Answer 1

Try the below query.

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

Here is MongoPlayground for you.