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.

score 2 · Accepted Answer · answered Jan 17 '22 at 12:32

2

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

answered Jan 17 '22 at 12:32

Tom Slabbaert

21,288
10
30
43

Tom, your changes still don't work. Any other solution? – JulioC Jan 18 '22 at 15:19
You need to provide sample data then for both collections, it's very hard to guess what other problems might exist. – Tom Slabbaert Jan 18 '22 at 15:35
Done, Tom. I copied the sample. – JulioC Jan 18 '22 at 16:13
It's the same issues, `$project` "deletes" the fields that you are not specifying, so when you try to use them in a later stage to match it will not work. look at this: https://mongoplayground.net/p/ukvqOomyS7b – Tom Slabbaert Jan 18 '22 at 16:17
If I deleted the $project, the query returns all records. https://mongoplayground.net/p/FEqIzLI7ZpV – JulioC Jan 18 '22 at 16:26
there is just a mismatch in the collection name, https://mongoplayground.net/p/KN6d9lp_Zi9. it's working fine – Tom Slabbaert Jan 18 '22 at 16:32
Sorry, but it isn't working fine. The result is "no document found" and it should return two documents (codes: 124 and 125) – JulioC Jan 18 '22 at 16:47
those are nested movies and not "documents", if you want those all you're missing is an `$unwind` before the lookup https://mongoplayground.net/p/Sff1nslGH_o – Tom Slabbaert Jan 18 '22 at 16:55
Eureka! Thanks, Tom. – JulioC Jan 18 '22 at 17:04
Tom, Is it possible to return an only string array with the movie codes? – JulioC Jan 18 '22 at 17:16
Not quite, the closest you can get is something like this: https://mongoplayground.net/p/mFH0FwIs2NS the aggregation framework always returns an array of documents (objects) – Tom Slabbaert Jan 18 '22 at 17:26
Tom, Could you help me in https://stackoverflow.com/questions/70776518/aggregation-between-a-mongodb-collection-and-an-external-array/70778067 ? – JulioC Jan 20 '22 at 10:56

MongoDB get documents from a collection not in other collection

1 Answers1