Mongodb nested document where clause returns many subdocuments

Question

I have a mongodb document that looks similar to this:

{
"id": 1,
"title": "This is the title",
"body" : "This is the body",
"comments": [
    {
        "email_address": "mirko.benedetti@somemail.com",
        "name": "Mirko",
        "surname": "Benedetti",
        "language": "it",
        "text": "This is a message",
        "published": "Y",
        "on": "2014-03-22 15:04:04"
    },
    {
        "email_address": "marc.surname@somemail.com",
        "name": "Marc",
        "surname": "Surname",
        "language": "it",
        "text": "Another Message",
        "published": "N",
        "on": "2014-03-23 15:04:05"
    }
  ]
}

And I have a query like this:

$this->db->collection->find(array('id' => $id, 'language' => $lang, 'comments.published' => 'Y'),
                        array('comments.name' => 1, 'comments.surname' => 1, 'comments.text' => 1, 'comments.on' => 1, '_id' => 0));

My problem is that running that query, mongodb returns both comments, which I don't want, I want only the message with "published": "Y".

I tried for example to run 'comments.published' => 'something' and none comment is selected, which is correct, but if at least one of the comments has the flag "published" set to 'Y', both comments are showed.

Any help will be welcome.

Answer 1

Look at $elemMatch documentation

db.schools.find( { zipcode: "63109" },
                 { students: { $elemMatch: { school: 102 } } } )