How to get only the records which are not contained in the array of another record-CodePudding

I'm trying to retrieve from MongoDB all records which are not referenced in another document's array field. I have data which is in the following format:

[
  { "domain": "foo.com", "altNames": [] },
  { "domain": "bar.com", "altNames": [] },
  { "domain": "zaz.com", "altNames": ["foo.com", "bar.com"]},
  { "domain": "baz.com", "altNames": ["boo.com"]}
]

From this data I want to retrieve records with domain zaz.com and baz.com due to their domain not being located in another records altNames array.

CodePudding user response：

Perform a self-lookup and check the result array is empty or not

db.collection.aggregate([
  {
    "$lookup": {
      "from": "collection",
      let: {
        d: "$domain"
      },
      pipeline: [
        {
          "$match": {
            $expr: {
              $in: [
                "$$d",
                "$altNames"
              ]
            }
          }
        },
        {
          $limit: 1
        }
      ],
      "as": "altNamesLookup"
    }
  },
  {
    "$match": {
      altNamesLookup: []
    }
  },
  {
    "$project": {
      altNamesLookup: false
    }
  }
])

Here is the Mongo playground for your reference.