Querying Subdocument and Returning Matching Subdoc

Question

cExample Mongo Document

{
  "_id": ObjectId("5652e77f21b0f1f2692558a1"),
  "category": "clothing",
  "Brand": [
    {
      "name": "Adidas",
      "key": "Adidas"
    },
    {
      "name": "Reebok",
      "key": "Reebok"
    },
    {
      "name": "Puma",
      "key": "Puma"
    }
  ],
  "Color": [
    {
      "name": "Red",
      "key": "Red"
    },
    {
      "name": "Green",
      "key": "Green"
    },
    {
      "name": "Blue",
      "key": "Blue"
    }
  ]
}

Now I want to search for Brands where name is either Adidas or Puma. Other requerement is to return sub document only so I tried query given below

find( { "Brand.name" : { $in : ["Reebok", "Adidas"]}},{"Brand" : 1, "_id" : 0})

But its returning me all three objects of Brand array

{
    "Brand" : [ 
        {
            "name" : "Adidas",
            "key" : "Adidas"
        }, 
        {
            "name" : "Reebok",
            "key" : "Reebok"
        }, 
        {
            "name" : "Puma",
            "key" : "Puma"
        }
    ]
}

I can sertainly do it through Javascript as well (Iterating through retuned array and metching values) but Is there any way to get my purpose solved with Mongo Query only.

Note : In NoSql Systems, where JSON documents are supported, biggest difference is these systems doesn't support relationships but you can always maintain relations by subdocuments. If such queries (mentioned above) are not possible then I think holding data in subdocument form is not good practise as you can not query it as you want. Expert Thoughts on this????

Answer 1

$in in find query is designed to return documents rather than sub documents. In your case mongoDB has provided the aggregation framework. This will help you filter sub documents.

For mongoDB <= 3.0.x

db.collection.aggregate(
  { $project: { Brand: 1}},
  { $unwind: '$Brand'},
  { $match: { "Brand.name" : { $in : ["Reebok", "Adidas"]}}},
  { $group: { _id: '$_id', Brand: {$push : '$Brand' }}}
)

MongoDB 3.2 way

db.collection.aggregate([
   {
      $project: {
         Brand: {
            $filter: {
               input: "$Brand",
               as: "Brand",
               cond: { "$$Brand.name": { $in : ["Reebok", "Adidas"]}}
            }
         }
      }
   }
])

Answer 2

It work correct. In the mongo documentation is written as follows:

The $in operator selects the documents where the value of a field equals any value in the specified array.

It returned you the document, that contain "Reebok" or "Adidas".