Given a list of ids, what's the best way to query which ids do not exist in the collection?

Question

I have a collection of documents which contain unique id field. Now I have a list of ids which may contain some ids that do not exist in the collection. What's the best way to find out those ids from the list?

I know I can use $in operator to get the documents which have ids contained in the list then compare with the given id list, but is there better way to do it?

Answer 1

I suppose you have the following documents in your collection:

{ "_id" : ObjectId("55b725fd7279ca22edb618bb"), "id" : 1 }
{ "_id" : ObjectId("55b725fd7279ca22edb618bc"), "id" : 2 }
{ "_id" : ObjectId("55b725fd7279ca22edb618bd"), "id" : 3 }
{ "_id" : ObjectId("55b725fd7279ca22edb618be"), "id" : 4 }
{ "_id" : ObjectId("55b725fd7279ca22edb618bf"), "id" : 5 }
{ "_id" : ObjectId("55b725fd7279ca22edb618c0"), "id" : 6 }

and the following list of id

var listId = [ 1, 3, 7, 9, 8, 35 ];

We can use the .filter method to return the array of ids that is not in your collection.

var result = listId.filter(function(el){
    return db.collection.distinct('id').indexOf(el) == -1; });

This yields

[ 7, 9, 8, 35 ] 

---

Now you can also use the aggregation frameworks and the $setDifference operator.

db.collection.aggregate([
   { "$group": { "_id": null, "ids": { "$addToSet": "$id" }}}, 
   { "$project" : { "missingIds": { "$setDifference": [ listId, "$ids" ]}, "_id": 0 }}
])

This yields:

{ "missingIds" : [ 7, 9, 8, 35 ] }

Answer 2

Unfortunately MongoDB can only use built in functions (otherwise I'd recommend using a set) but you could try and find all distinct id's in your list then just manually pull them out.

Something like (untested):

var your_unique_ids = ["present", "not_present"];

var present_ids = db.getCollection('your_col').distinct('unique_field', {unique_field: {$in: your_unique_ids}});

for (var i=0; i < your_unique_ids.length; i++) {
    var some_id = your_unique_ids[i];
    if (present_ids.indexOf(some_id) < 0) {
        print(some_id);
    }
}