Which Aggregate function in mongoDB should I use?

39 views Asked by At

I have a collection as follows:

{
    "_id": {
        "$oid": "6100b7c226aa5c7c0bb665e1"
    },
    "empId": "101962700",
    "Result": "NotEnrolled",
    "enrollDate": "4/21/2021",
    "Name": "THOMAS Edison",
    "Flag": "NEGATIVE",
    "createdDateTime": {
        "$date": "2021-06-30T06:00:00.000Z"
    }
} 
{
    "_id": {
        "$oid": "6100b7c226aa5c7c0bb665e1"
    },
    "empId": "101962700",
    "Result": "NotEnrolled",
    "enrollDate": "5/21/2021",
    "Name": "THOMAS Edison",
    "Flag": "NEGATIVE",
    "createdDateTime": {
        "$date": "2021-06-30T06:00:00.000Z"
    }
}
{
    "_id": {
        "$oid": "6100b7c226aa5c7c0bb665e1"
    },
    "empId": "101962700",
    "Result": "NotEnrolled",
    "enrollDate": "5/21/2021",
    "Name": "THOMAS Edison",
    "Flag": "NEGATIVE",
    "createdDateTime": {
        "$date": "2021-06-30T06:00:00.000Z"
    }
}

{
    "_id": {
        "$oid": "6100b7c226aa5c7c0bb665e1"
    },
    "empId": "101962701",
    "Result": "Enrolled",
    "enrollDate": "4/21/2021",
    "Name": "Jim Miller",
    "Flag": "NEGATIVE",
    "createdDateTime": {
        "$date": "2021-06-30T06:00:00.000Z"
    }
} 
{
    "_id": {
        "$oid": "6100b7c226aa5c7c0bb665e1"
    },
    "empId": "101962701",
    "Result": "Enrolled",
    "enrollDate": "5/21/2021",
    "Name": "Jim Miller",
    "Flag": "NEGATIVE",
    "createdDateTime": {
        "$date": "2021-06-30T06:00:00.000Z"
    }
}
{
    "_id": {
        "$oid": "6100b7c226aa5c7c0bb665e1"
    },
    "empId": "101962701",
    "Result": "Enrolled",
    "enrollDate": "5/21/2021",
    "Name": "Jim Miller",
    "Flag": "NEGATIVE",
    "createdDateTime": {
        "$date": "2021-06-30T06:00:00.000Z"
    }
}

Now by mistake 20 records for same empID with the same date ('5/21/2021') got inserted in the DB. I want to keep only 1 record for that date and employee in the DB and delete rest 19 records. that means for empID I only want to keep record with "enrollDate":"4/21/2021" and 1 record with "enrollDate":"5/21/2021" and delete duplicate records for the "enrollDate":"5/21/2021". Same for "empId":"101962701".

How can I form delete query in Mongodb?

Expected Output

{
    "_id": {
        "$oid": "6100b7c226aa5c7c0bb665e1"
    },
    "empId": "101962700",
    "Result": "NotEnrolled",
    "enrollDate": "4/21/2021",
    "Name": "THOMAS Edison",
    "Flag": "NEGATIVE",
    "createdDateTime": {
        "$date": "2021-06-30T06:00:00.000Z"
    }
} 
{
    "_id": {
        "$oid": "6100b7c226aa5c7c0bb665e1"
    },
    "empId": "101962700",
    "Result": "NotEnrolled",
    "enrollDate": "5/21/2021",
    "Name": "THOMAS Edison",
    "Flag": "NEGATIVE",
    "createdDateTime": {
        "$date": "2021-06-30T06:00:00.000Z"
    }
}

{
    "_id": {
        "$oid": "6100b7c226aa5c7c0bb665e1"
    },
    "empId": "101962701",
    "Result": "Enrolled",
    "enrollDate": "4/21/2021",
    "Name": "Jim Miller",
    "Flag": "NEGATIVE",
    "createdDateTime": {
        "$date": "2021-06-30T06:00:00.000Z"
    }
} 

{
    "_id": {
        "$oid": "6100b7c226aa5c7c0bb665e1"
    },
    "empId": "101962701",
    "Result": "Enrolled",
    "enrollDate": "5/21/2021",
    "Name": "Jim Miller",
    "Flag": "NEGATIVE",
    "createdDateTime": {
        "$date": "2021-06-30T06:00:00.000Z"
    }
}

I want to remove duplicate empId with enrollDate.

1

There are 1 answers

0
Wernfried Domscheit On

Would be this one:

db.collection.aggregate([
  {
    $group: {
      _id: { empId: "$empId", enrollDate: "$enrollDate" },
      data: { $first: "$$ROOT" }
    }
  },
  { $replaceRoot: { newRoot: "$data" } }
])

Mongo playground