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?

I'd do $match -> $unwind -> $replaceRoot -> $match -> $project. — aderchox
– aderchox, Commented Nov 3, 2022 at 8:17

SuleymanSah · Accepted Answer · 2020-04-05 16:55:19Z

7

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

edited Apr 5, 2020 at 16:55

SuleymanSah

18k6 gold badges38 silver badges60 bronze badges

answered Sep 5, 2016 at 23:06

Reuben L.

2,8592 gold badges31 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Krisalay Over a year ago

This will give me the products of user2 too, which i dont want.

Reuben L. Over a year ago

Ah I missed that requirement, you can just add that as an additional param.

Reuben L. Over a year ago

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

Karlo Tvrdinic · Accepted Answer · 2020-03-11 18:27:15Z

1

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.

edited Mar 11, 2020 at 18:27

answered Mar 11, 2020 at 18:19

Karlo Tvrdinic

395 bronze badges

Comments

Rahul · Accepted Answer · 2016-09-05 23:21:21Z

0

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

answered Sep 5, 2016 at 23:21

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

2 Comments

Krisalay Over a year ago

It will not work because finally it is going to return the whole data with "username" as "user1"

Reuben L. Over a year ago

In that case you should use $unwind

Collectives™ on Stack Overflow

mongoDB query to find the document in nested array

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related