mongoDB query to find the document in nested array

Question

[{
  "username":"user1",
  "products":[
           {"productID":1,"itemCode":"CODE1"},
           {"productID":2,"itemCode":"CODE1"},
           {"productID":3,"itemCode":"CODE2"},
       ]
},
{
  "username":"user2",
  "products":[
           {"productID":1,"itemCode":"CODE1"},
           {"productID":2,"itemCode":"CODE2"},
       ]
}]

I want to find all the "productID" of "products" for "user1" such that "itemCode" for the product is "CODE1".

What query in mongoDB should be written to do so?

Answer 1

If you only need to match a single condition, then the dot notation is sufficient. In Mongo shell:

db.col.find({"products.itemCode" : "CODE1", "username" : "user1"})

This will return all users with nested product objects having itemCode "CODE1".

Updated

Wasn't clear on your requirements at first but this should be it.

If you want each product as a separate entry, then you would need to use the aggregate framework. First split the entries in the array using $unwind, then use $match for your conditions.

db.col.aggregate(
  { $unwind: "$products" },
  { $match: { username: "user1", "products.itemCode": "CODE1" } }
);

response:

{ "_id" : ObjectId("57cdf9c0f7f7ecd0f7ef81b6"), "username" : "user1", "products" : { "productID" : 1, "itemCode" : "CODE1" } }
{ "_id" : ObjectId("57cdf9c0f7f7ecd0f7ef81b6"), "username" : "user1", "products" : { "productID" : 2, "itemCode" : "CODE1" } }

Answer 2

The answer to your question is

db.col.aggregate([
   { $unwind: "$products" },
   { $match: { username: "user1", "products.itemCode": CODE1 } },
   { $project: { _id: 0, "products.productID": 1 } }
]);

In my case didn't work without [ ] tags.