MongoDB get documents from a collection not in other collection

Question

I'm trying to get the documents from a collection where aren't in other collection (the common NOT IN clause in SQL).

If I run the next query:

db.Companies_Movies.aggregate([
    {
        $project: 
        {
            "CompanyList.Movies.Code" : 1
        }
    },
    {
        $match: 
        {
             "CompanyList.CodeCompany": "23"
        }
    },
    {   
        $lookup:    
        {
            from: "Movies",
            localField: "CompanyList.Movies.Code",
            foreignField: "Movie.Code",
            as: "matched_docs"
        }
    }
]);

This query shows the movies includes in CompanyList.Movies.Code and in Movie.Code. Good. But I just have the rest of movies includes in CompanyList.Movies whose codes aren't included in Movie.Code.

As Nikos Tsagkas said in Get "data from collection b not in collection a" in a MongoDB shell query it should be sufficient to include the following sentence:

    {
        $match: { "matched_docs": { $eq: [] } }
    }

But when I run my final code, it doesn't returns anything:

    db.Companies_Movies.aggregate([
    {
        $project: 
        {
            "CompanyList.Movies.Code" : 1
        }
    },
    {
        $match: 
        {
            "CompanyList.CodeCompany": "23"
        }
    },
    {
        $lookup:    
        {
            from: "Movies",
            localField: "CompanyList.Movies.Code",
            foreignField: "Movie.Code",
            as: "matched_docs"
        }
    },
    {
        $match: { "matched_docs": { $eq: [] } }
    }
    ]);

There are 59 documents that are not returned by this code.

This is my pipeline I've created in MongoDB Compass after Tom's changes and it still doesn't work:

[{
    $match: 
     {
        'CompanyList.CodeCompany': '23'
     }
     },
     {
          $lookup: 
          {
               from: 'Movies',
               localField: 'CompanyList.Movies.Code',
               foreignField: 'Movie.Code',
               as: 'docs'
          }
     }, 
     {
          $project: 
          {
               'CompanyList.Movies.Code': 1,
               'CompanyList.CodeCompany': 1
          }
     }, 
     {
          $match: 
          {
               docs:{  $eq: [] }
          }
    }]

If I delete the $project, it not works either.

Sample Data (reduced)

Companies_Movies collection:

{
_id:ObjectId("61bf47b974641866e1244e65"),
"CompanyList": {
    "CodeCompany": "23",
    "NameCompany": "Company Name Entertainment",
    "Movies": [{
        "Code": "123",
        "Name": "Title 1",
        "Order": 1,
        "UserDescription": null
    }, {
        "Code": "124",
        "Name": "Title 2",
        "Order": 2,
        "UserDescription": null
    }, {
        "Code": "125",
        "Name": "Title 3",
        "Order": 3,
        "UserDescription": null
    }],
    "DateInserted": {
        "$date": "2021-12-13T17:30:06.824Z"
    }
  }
}

Movies collection:

[{
_id:ObjectId("61bf57bc9d1f93b7ae5fa785"),
"Movie": {
    "Code": "123",
    "OriginalTitle": "Title 1",
    "Year": 2021
 },
_id:ObjectId("61bf57bc9d1f93b7ae5fa786"),
"Movie": {
    "Code": "124",
    "OriginalTitle": "Title 2",
    "Year": 2021
 },
_id:ObjectId("61bf57bc9d1f93b7ae5fa787"),
"Movie": {
    "Code": "125",
    "OriginalTitle": "Title 3",
    "Year": 2021
 },
_id:ObjectId("61bf57bc9d1f93b7ae5fa788"),
"Movie": {
    "Code": "126",
    "OriginalTitle": "Title 4",
    "Year": 2021
 }
}]

Anyone know what might be happening?

Thanks to everyone.

user_10770370 · Answer

This is simply caused by your $project stage, after you run:

{
  $project: {
    "CompanyList.Movies.Code" : 1
  }
},

You're data will look like this:

{
   CompanyList: [
       {
          Movies: { code: "123", ... other fields } 
       }
   ]
}

Now you're trying to match "CompanyList.CodeCompany": "23", but the field CodeCompany simply does not exist anymore as you did not provide it in the project stage.

So just change you're projection stage to include fields you will use in later stages:

{
  $project: {
    "CompanyList.Movies.Code" : 1,
    "CompanyList.CodeCompany": 1
  }
},

MongoDB get documents from a collection not in other collection

1 Answers