finding document in mongodb with specific id and username

Question

{
    "data": [
        {
          "_id": 555,
          "username": "jackson",
          "status": "i am coding",
          "comments": [
            {
              "user": "bob",
              "comment": "bob me "
            },
            {
              "user": "daniel",
              "comment": "bob the builder"
            },
            {
              "user": "jesus",
              "comment": "bob the builder"
            },
            {
              "user": "hunter",
              "comment": "bob the builder"
            },
            {
              "user": "jeo",
              "comment": "bob the builder"
            },
            {
              "user": "jill",
              "comment": "bob the builder"
            }
          ]
        }
    ]
}

so i want to get the result with _id :555 and user:bob i tried with below code but i cant make it work it returns empty array

app.get('/all',function(req , res){
  db.facebook.find({_id:555},{comments:[{user:"bob"}]},function(err,docs){res.send(docs,{data:docs});});
} );

i want the result to be like this listed below with the comment with user:bob

{
    "_id": 555,
    "username": "jackson",
    "status": "i am coding",
    "comments": [
        {
        "user": "bob",
        "comment": "bob me "
        }
    ]
}

Answer 1

Only aggregate or mapReduce could exclude items from subarray in output. Shortest is to use $redact:

db.facebook.aggregate({
 $redact:{
      $cond:{ 
         if:{$and:[{$not:"$username"},{$ne:["$user","bob"]}]}, 
         then: "$$PRUNE", 
         else: "$$DESCEND" 
      }
 }
})

Explanation: $reduct would be applied to each subdocument starting from whole document. For each subdocument $reduct would either prune it or descend. We want to keep top level document, that is why we have {$not:"$username"} condition. It prevents top level document from pruning. On next level we have comments array. $prune would apply condition to each item of comments array. First condition {$not:"$username"} is true for all comments, and second condition {$ne:["$user","bob"]} is true for all subdocuments where user!="bob", so such documents would be pruned.

Update: in node.js with mongodb native driver

db.facebook.aggregate([{
 $redact:{
      $cond:{ 
         if:{$and:[{$not:"$username"},{$ne:["$user","bob"]}]}, 
         then: "$$PRUNE", 
         else: "$$DESCEND" 
      }
 }
}], function(err,docs){})

One more thing: $prune is new operator and available only in MongoDB 2.6