Find documents in MongoDB where value of field does not exist in other collection

Question

I have child documents that failed to get archived when their parent userFolder was deleted. The link from the child documents is set as "group" field, which is the string version of the objectID of their parent.

How can I do an aggregation that would give me the list of documents where "group" value is not found in the objectIDs of the userFolder collection

Answer 1

Something like this should work, you will need to substitute collection and fields names as appropriate:

db.child_collection.aggregate([
  {
    $lookup: {
      from: "parent_collection",
      let: {
        parent_group: {
          $toObjectId: "$group"
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$_id",
                "$$parent_group"
              ]
            }
          }
        }
      ],
      as: "parent_docs"
    }
  },
  {
    $match: {
      "parent_docs": {
        $size: 0
      }
    }
  }
])

After the $lookup stage , each document will have a parent_docs array. If the child document has an undeleted parent, this array will have one element. Otherwise it's orphaned and the array will be empty. You're interested in finding the orphaned documents, so we filter for arrays with a size of 0.

Working Mongo Playground