Mirko Benedetti Mirko Benedetti - 9 months ago 46
PHP Question

Mongodb nested document where clause returns many subdocuments

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 Source

Look at $elemMatch documentation

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