MongoDB. Having identified a list of ids, what's the best way to query which ids don't exist in the collection?
I have a set of documents that contain a unique id field. I now have a list of ids that may contain some ids that don't exist in the collection. What's the best way to find out these IDs from the list?
I know I can use the $ in operator to get documents with the IDs contained in a list and then compare against the specified list of IDs, but is there a better way to do this?
source to share
Unfortunately MongoDB can only use built-in functions (otherwise I would recommend using set
), but you could try to find all the individual ids in your list and then just pull them out manually.
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);
}
}
source to share
I assume 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 id
var listId = [ 1, 3, 7, 9, 8, 35 ];
We can use a method to return an array that is not in your collection. .filter
ids
var result = listId.filter(function(el){
return db.collection.distinct('id').indexOf(el) == -1; });
This gives
[ 7, 9, 8, 35 ]
EDIT (Based on @YathishManjunath answer)
Now you can also use aggregation structures and . $setDifference
db.collection.aggregate([
{ "$group": { "_id": null, "ids": { "$addToSet": "$id" }}},
{ "$project" : { "missingIds": { "$setDifference": [ listId, "$ids" ]}, "_id": 0 }}
])
This gives:
{ "missingIds" : [ 7, 9, 8, 35 ] }
source to share