Get "data from collection b not in collection a" in a MongoDB shell query

Question

I have two MongoDB collections that share a common _id. Using the mongo shell, I want to find all documents in one collection that do not have a matching _id in the other collection.

Example:

> db.Test.insert({ "_id" : ObjectId("4f08a75f306b428fb9d8bb2e"), "foo" : 1 }) > db.Test.insert({ "_id" : ObjectId("4f08a766306b428fb9d8bb2f"), "foo" : 2 }) > db.Test.insert({ "_id" : ObjectId("4f08a767306b428fb9d8bb30"), "foo" : 3 }) > db.Test.insert({ "_id" : ObjectId("4f08a769306b428fb9d8bb31"), "foo" : 4 }) > db.Test.find() { "_id" : ObjectId("4f08a75f306b428fb9d8bb2e"), "foo" : 1 } { "_id" : ObjectId("4f08a766306b428fb9d8bb2f"), "foo" : 2 } { "_id" : ObjectId("4f08a767306b428fb9d8bb30"), "foo" : 3 } { "_id" : ObjectId("4f08a769306b428fb9d8bb31"), "foo" : 4 } > db.Test2.insert({ "_id" : ObjectId("4f08a75f306b428fb9d8bb2e"), "bar" : 1 }); > db.Test2.insert({ "_id" : ObjectId("4f08a766306b428fb9d8bb2f"), "bar" : 2 }); > db.Test2.find() { "_id" : ObjectId("4f08a75f306b428fb9d8bb2e"), "bar" : 1 } { "_id" : ObjectId("4f08a766306b428fb9d8bb2f"), "bar" : 2 } 

Now I want some query or queries that returns the two documents in Test where the _id's do not match any document in Test2:

{ "_id" : ObjectId("4f08a767306b428fb9d8bb30"), "foo" : 3 } { "_id" : ObjectId("4f08a769306b428fb9d8bb31"), "foo" : 4 } 

I've tried various combinations of $not, $ne, $or, $in but just can't get the right combination and syntax. Also, I don't mind if db.Test2.find({}, {"_id": 1}) is executed first, saved to some variable, which is then used in a second query (though I can't get that to work either).

Update: Zachary's answer pointing to the $nin answered the key part of the question. For example, this works:

> db.Test.find({"_id": {"$nin": [ObjectId("4f08a75f306b428fb9d8bb2e"), ObjectId("4f08a766306b428fb9d8bb2f")]}}) { "_id" : ObjectId("4f08a767306b428fb9d8bb30"), "foo" : 3 } { "_id" : ObjectId("4f08a769306b428fb9d8bb31"), "foo" : 4 } 

But (and acknowledging this is not scalable but trying to it anyway because its not an issue in this situation) I still can't combine the two queries together in the shell. This is the closest I can get, which is obviously less than ideal:

vals = db.Test2.find({}, {"_id": 1}).toArray() db.Test.find({"_id": {"$nin": [ObjectId(vals[0]._id), ObjectId(vals[1]._id)]}}) 

Is there a way to return just the values in the find command so that vals can be used directly as the array input to $nin?

Answer 1

In mongo 3.2 the following code seems to work

db.collectionb.aggregate([   {     $lookup: {         from: "collectiona",         localField: "collectionb_fk",         foreignField: "collectiona_fk",         as: "matched_docs"       }    },    {       $match: {         "matched_docs": { $eq: [] }       }    } ]); 

based on this https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#use-lookup-with-an-array example