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.

I tried to add one more comment with "published" set to "N", and it gives me 3 records, with var_dump, the resulting array is of 3 elements. — Mirko Benedetti, Nov 18 '16 at 15:50

score 1 · Accepted Answer · answered Nov 18 '16 at 15:51

1

Look at $elemMatch documentation

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

answered Nov 18 '16 at 15:51

Orelsanpls

22,456
6
42
69

s7vr · Answer 2 · 2016-11-19T17:14:49.447

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
        }
    }
}])

edited Nov 19 '16 at 17:14

answered Nov 18 '16 at 19:17

s7vr

73,656
11
106
127

My problem is also that I have to select "comments" inside a certain document ("blog_post") but I have also to select the **right document** (blog_post) and that is not very intuitive from the documentation, with aggregate. – Mirko Benedetti Nov 19 '16 at 13:02
1

you will just need to add match stage, considering the query criteria same as what we use in find method Updated answer. Hope thats what you meant from your previous comment. – s7vr Nov 19 '16 at 13:24
What if I want to pick only certain elements of "comments"? – Mirko Benedetti Nov 19 '16 at 16:57
1

you will just need to add another project to include certain fields from nested array. Updated answer. – s7vr Nov 19 '16 at 17:14
Finally I had to sort the nested documents "comments", but the only way to make it work was to use $unwind before, like this: `{'$unwind' : '$comments'}, {'$sort': {'comments.on' => -1}}` is it correct? – Mirko Benedetti Nov 21 '16 at 14:09
1

Yes unwind is required if you needed to sort the nested array document. – s7vr Nov 22 '16 at 13:07

Mongodb nested document where clause returns many subdocuments

2 Answers2