I have collection of documents like following:
{
_id: ObjectId("55b164c65c1a8f360078c917"),
message_id: ["abc","def"],
message: [{message_id: "efgh", message_body: "somebody"}]
}
I want to find out mongodb query to find out those document whose message.message_id is not in message_id. Can anyone please help me with this.
Probably the best approach with MongoDB 2.6 and greater uses .aggregate() with $redact:
db.collection.aggregate([
{ "$redact": {
"$cond": {
"if": {
"$gt": [
{ "$size": {
"$setIntersection": [
{ "$map": {
"input": "$message",
"as": "msg",
"in": "$$msg.message_id"
}},
"$message_id"
]
}},
0
]
},
"then": "$$PRUNE",
"else": "$$KEEP"
}
}}
])
The "inside out" of the logic here is that $map is used to take out the keys of "message_id" from the "messages" array, and return that for comparison with the "message_id" array itself to see the result of $setIntersection. It's done this way to see "intersection" as there is nothing concrete here that says one is the "subset" of the other. So it's just the common elements, otherwise there is $setIsSubset.
Naturally if the $size of that intersection is greater than 0, then there was a match of elements. So that true condition would be "pruned", and anything else is "kept".
Or in prior versions use $where with this JavaScript eval instead, follwing exactly the same logic:
db.collection.find({
"$where": function() {
var self = this;
return self.message.map(function(el) {
return el.message_id;
}).filter(function(el) {
return self.mesage_id.indexOf(el) != -1
}).length == 0;
}
})
Another take on an aggregation pipeline is also possible in earlier versions, but it would take several pipeline stages and therefore several passes to complete. Therefore that would not be your best option pre MongoDB 2.6.
At any rate, standard query operations cannot normally "compare" one element of a document to another. So this leaves the aggregation framework or $where as the two tools that can do this comparison.
