How to remove documents with broken reference in MongoDB?

Question

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?

Answer 1

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) }
})

Answer 2

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( function(myDoc) {
    var cursor = db.authority.find({'_id' : myDoc.authority_id});
    if(cursor.hasNext() == false) {
        db.user.remove({_id : myDoc._id});
    }
});