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

You need to be careful while using the elemMatch operator. First thing it has two variants. $elemMatch(projection) & $elemMatch(query)

The elemMatch(projection) variant appears to working because the filter criteria you have only matches to one value in comments array.

The below query will work fine.

find({'_id' : ObjectId("582f2abf9b549b5a765ab380"), comments: { $elemMatch: { language: "it", published : "Y" }}})

Now consider when you have more than 1 matching values (two values with 'Y' published status) in comments arrays, then the above query will not work and will only return the first matching value.

In this scenario, you will need to use $filter, which will filter the comments array based on the filter crtieria passed.

aggregate([{
    $match: {
        '_id': ObjectId("582f2abf9b549b5a765ab380")
    }
}, {
    "$project": {
        "comments": {
            "$filter": {
                "input": "$comments",
                "as": "result",
                "cond": {
                    $and: [{
                        $eq: ["$$result.language", "it"]
                    }, {
                        $eq: ["$$result.published", "Y"]
                    }]
                }
            }
        }
    }
}, {
    $project: {
        "comments": {
            name: 1,
            surname: 1,
            text: 1,
            on: 1
        }
    }
}])

Answer 2

Look at $elemMatch documentation

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