MongoDB-return only matching element in nested array of documents

Question

I have below kind of data in my mongo database:

[
   { 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: "G", qty: 15 } ] },
   { item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
   { item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "G", qty: 35 } ] }
]

I want to find all matching document that has warehouse as G. The instock array should contain only the matching element. Basically, the output should look like below:

[{ item: "paper", instock: [  { warehouse: "G", qty: 15 } ] },
 { item: "postcard", instock: [  { warehouse: "G", qty: 35 } ] }
]

I have written below query:

db.collection_name.find({"instock.warehouse":"G"});

Above query returns required documents, but it gives all elements of instock array, which I don't want.

How can I write such query which gives me desired documents with only matching elements of instock array?

Thank You!:)

Does this answer your question? [Retrieve only the queried element in an object array in MongoDB collection](https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — rickhg12hs, Nov 14 '22 at 11:57

score 0 · Accepted Answer · answered Nov 14 '22 at 12:06

You can use this aggregation pipeline:

First $match to get only documents where exists at least one "G" into the array (note that only this step is like the find you are doing).
And the use$set to overwrite the field instock only with values which match the filter "wharehouse equals G".

db.collection.aggregate([
  {
    "$match": {
      "instock.warehouse": "G"
    }
  },
  {
    "$set": {
      "instock": {
        "$filter": {
          "input": "$instock",
          "cond": {
            "$eq": [
              "$$this.warehouse",
              "G"
            ]
          }
        }
      }
    }
  }
])

Example here

Also note that you can use $elemMatch with a find query but be careful because $elemMatch only return one element.

Check this example where I've added a new warehouse into "paper" but is not displayed but it does using the previous aggregation query.

MongoDB-return only matching element in nested array of documents

1 Answers1