CODEWITHSUNDEEP
mongodbmongodb-query
Krisalay
Krisalay

Reputation: 235

mongoDB query to find the document in nested array

[{
  "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?

Upvotes: 7

Views: 11778

Answers (3)

Reuben L.
Reuben L.

Reputation: 2859

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" } }

Upvotes: 7

Karlo Tvrdinic
Karlo Tvrdinic

Reputation: 29

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.

Upvotes: 1

Rahul
Rahul

Reputation: 77846

You need multiple filter for this like below which is nothing but AND condition (assuming your collection name is collection1)

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

Upvotes: 0

Related Questions