Find a value within embedded document based on another value from the embedded document

[Sai_Deeksh]

This help document shows how to query a nested document. https://www.mongodb.com/docs/manual/tutorial/query-array-of-documents/

Taking the same example as in the above link, I have a requirement where I need to find what is the ‘qty’ value for item = “journal” and instock- warehouse = “A”. Please share your ideas on how this can be done efficiently.

db.inventory.insertMany( [
   { item: "journal", instock: [ { warehouse: "A", qty: 5 }, { warehouse: "C", qty: 15 } ] },
   { item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] },
   { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] },
   { item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
   { item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);

[Tarun_Gaur]

Hello @Sai_Deeksh ,

Welcome to The MongoDB Community Forums!

To find the value of qty for an item equal to “journal” and instock.warehouse equal to “A”, you can try below query

db.inventory.find(
   {
      item: "journal",
      "instock.warehouse": "A"
   },
   {
      "instock.$": 1
   }
)

This query uses the dot notation to access the nested fields. The $ positional operator in the projection ("instock.$": 1) ensures that only the matching element of the instock array is returned in the result.

Output

{
  _id: ObjectId("64626b267d66abce46242ac0"),
  instock: [
    {
      warehouse: 'A',
      qty: 5
    }
  ]
}

Note: Please test as per ypur use case and update it as per the requirements.

Regards,
Tarun

[Sai_Deeksh]

Thanks Gaurav. The find command extracts the required embedded document.

I also found another thread where an aggregation solution was provided and that worked fine too
edt-confluence.nam.nsroot.net/confluence/display/C153250A/Important+Links