CODEWITHSUNDEEP
mongodb
romaninsh
romaninsh

Reputation: 10664

How to remove documents with broken reference in MongoDB?

I have two collections in Mongo:

db.user.find():
{
  "_id": { "$oid" : "52db05e6a2cb2f36afd63c47" },
  "name": "John",
  "authority_id": { "$oid" : "52daf174a2cb2f62aed63af3" },
}
{
  "_id": { "$oid" : "52db05e6a2cb2f36afd63d00" },
  "name": "Joe",
  "authority_id": { "$oid" : "52daf174a2cb2f62aed63af3" },
}

and

db.authority.find():
{
  "_id": { "$oid" : "52daf174a2cb2f62aed63af3" },
  "name": "Sample Authority"
}

Users store reference to authority's ID through ObjectId.

Now my problem: Several authorities have been deleted and are no longer in collection. I need to find a way how to iterate through the "user" collection and delete them if their authority_id is pointing to deleted authority.

I have tried this:

db.user.find(
    { 
      $where: function() { 
        db.authority.find({ _id: this.authority_id }).count() == 0  
      }
     })

but "db" is not accessible there. Is it possible to implement reference check inside iteration?

Upvotes: 9

Views: 5491

Answers (3)

Parvin Gasimzade
Parvin Gasimzade

Reputation: 26012

You can remove broken entries by iterating over cursor on the javascript shell or by using any Mongo driver. The following example will give you an idea to do it on javascript shell.

db.user.find().forEach((user) => {
    const authority = db.authority.findOne({'_id' : user.authority_id});

    if(!authority) db.user.remove({_id : user._id});
});

Upvotes: 5

Rui Castro
Rui Castro

Reputation: 421

You can use an aggregate to find all orphan users and then remove them.

const orphanUsers = db.user.aggregate([
    {
      // Join authority collection using authority_id
      $lookup: {
        from: "authority",
        localField: "authority_id",
        foreignField: "_id",
        as: "authority"
      }
    },
    // filter users without authority (means authority_id doesn't exist)
    { $match: { authority: [] } },
    // return only the _id
    { $project: { _id: "$_id" } }
])

// Delete all orphan users
db.user.deleteMany({
    _id: { $in: orphanUsers.map(({ _id }) => _id) }
})

Upvotes: 7

yǝsʞǝla
yǝsʞǝla

Reputation: 16412

"$where operator expressions cannot access certain global functions or properties, such as db, that are available in the mongo shell" according to http://docs.mongodb.org/manual/reference/operator/query/where/.

But you can try map reduce: http://cookbook.mongodb.org/patterns/pivot/

I would just do it in code personally, but you might have different requirements.

Upvotes: 0

Related Questions