Mongo find a subdocument with projection

Question

I encountered a weird problem when I tried to find a specific subdocument. For example, I have a collection named "test" with a document looks like:

{
  "_id": ObjectId("55af71b52028d8a365430fcd"),
  "sub": [{
    "name": "A",
    "type": "2",
    "score": "10"
  }, {
    "name": "B",
    "type": "2",
    "score": "9"
  }]
}

When I tried using query like db.getCollection('test').find({"sub.score" : "9","sub.type" : "2"},{"sub.$" :1}) to find the second subdocument, I got the first subdocument instead

{
  "_id": ObjectId("55af71b52028d8a365430fcd"),
  "sub": [{
    "name": "A",
    "type": "2",
    "score": "10"
  }]
}

Is it the expected result from the query? Why I get the first subdocument instead of the second subdocument, meanwhile the first subdocument score isn't the same as score in the query?

Yes it is expected behavior and it is a duplicate of [Retrieve only the queried element in an object array in MongoDB collection](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection). See the answers there mentioning `.aggregate()` for ways to get more than one match. — Blakes Seven, Jul 22 '15 at 11:26
@BlakesSeven thanks for fast reply. I need only one match and I can't find out why its return the first subdocument, meanwhile its score ("10") isn't what I put in the query ("9"). Is the condition executed as OR operation instead of AND operation? — Karsten Ari Agathon, Jul 22 '15 at 11:51
Read the explaination and understand the "why?" as explained there. — Blakes Seven, Jul 22 '15 at 12:04

Blakes Seven · Accepted Answer · 2015-07-22T12:03:49.657

The answer to getting what you want here is to use $elemMatch:

db.getCollection('test').find(
    {
        "sub": { "$elemMatch": { "score" : "9","type" : "2"} }
    },
    { "sub.$": 1 }
)

Which produces:

{
    "_id" : ObjectId("55af71b52028d8a365430fcd"),
    "sub" : [
            {
                    "name" : "B",
                    "type" : "2",
                    "score" : "9"
            }
    ]
}

Why?

Because when you referenced "both" properties as a condition of your "query" those properties just look for "any" match in the array. The rules of the positional $ operator used in projection are that the "first" match is the one presented.

In this case the "sub.type": 2 matches the very first index item of the array as well as the second. Therefore the "first" index matched ( first element ) is the one returned.

On the other hand, $elemMatch needs "both" conditions to be met on the same "element", not just valid for the document. So it is a "query" within a "query" and that is how you get the result.

Thanks for your help! Would you kindly explain why my query isn't work? I am really curious. — Karsten Ari Agathon, Jul 22 '15 at 12:06

Mongo find a subdocument with projection

1 Answers1