Delete/Remove query to remove duplicate records in MongoDB

Question

{
    "_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"
    }
}

I have collection as above. 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/remove rest 19 records. that means for empID I only want to keep 1 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".

Can someone help me to form delete/remove query in Mongodb?

Check this answer https://stackoverflow.com/a/19066328/13211263. It should help you. — lpizzinidev, Sep 08 '21 at 13:06

score 0 · Answer 1 · answered Sep 03 '21 at 15:25

you can use the delete query that will filter out the document you want to keep. for example:

db.collection.remove( { $and:[ {enrollDate: { $eq: "5/21/2021" } }}, {empId: "101962700"}, {_id: {$ne: {{your doc id you want to keep}} }])

in general to avoid those cases in the future i recommend you to add a unique index that will include the enrollDate and empId you can check the official mongo documentation https://docs.mongodb.com/manual/core/index-unique/

in addition use the proper type such as date, object id etc.

There are almost 500000 docs. I can not add Ids for each doc. Just wanted to check is there any way we can group by empId,date and keep only 1 doc for that date and remove remaining — Sandeep Deshpande, Sep 03 '21 at 22:21

Delete/Remove query to remove duplicate records in MongoDB

1 Answers1