Retrieve only the queried element in an object array in MongoDB collection

Question

Suppose you have the following documents in my collection:

{  
   "_id":ObjectId("562e7c594c12942f08fe4192"),
   "shapes":[  
      {  
         "shape":"square",
         "color":"blue"
      },
      {  
         "shape":"circle",
         "color":"red"
      }
   ]
},
{  
   "_id":ObjectId("562e7c594c12942f08fe4193"),
   "shapes":[  
      {  
         "shape":"square",
         "color":"black"
      },
      {  
         "shape":"circle",
         "color":"green"
      }
   ]
}

---

Do query:

db.test.find({"shapes.color": "red"}, {"shapes.color": 1})

Or

db.test.find({shapes: {"$elemMatch": {color: "red"}}}, {"shapes.color": 1})

---

Returns matched document (Document 1), but always with ALL array items in shapes:

{ "shapes": 
  [
    {"shape": "square", "color": "blue"},
    {"shape": "circle", "color": "red"}
  ] 
}

However, I'd like to get the document (Document 1) only with the array that contains color=red:

{ "shapes": 
  [
    {"shape": "circle", "color": "red"}
  ] 
}

How can I do this?

Freitas

Answer 1

MongoDB 2.2's new $elemMatch projection operator provides another way to alter the returned document to contain only the first matched shapes element:

db.test.find(
    {"shapes.color": "red"}, 
    {_id: 0, shapes: {$elemMatch: {color: "red"}}});

Returns:

{"shapes" : [{"shape": "circle", "color": "red"}]}

In 2.2 you can also do this using the $ projection operator, where the $ in a projection object field name represents the index of the field's first matching array element from the query. The following returns the same results as above:

db.test.find({"shapes.color": "red"}, {_id: 0, 'shapes.$': 1});

MongoDB 3.2 Update

Starting with the 3.2 release, you can use the new $filter aggregation operator to filter an array during projection, which has the benefit of including all matches, instead of just the first one.

db.test.aggregate([
    // Get just the docs that contain a shapes element where color is 'red'
    {$match: {'shapes.color': 'red'}},
    {$project: {
        shapes: {$filter: {
            input: '$shapes',
            as: 'shape',
            cond: {$eq: ['$$shape.color', 'red']}
        }},
        _id: 0
    }}
])

Results:

[ 
    {
        "shapes" : [ 
            {
                "shape" : "circle",
                "color" : "red"
            }
        ]
    }
]

Boulevard

any solution if I want it to return every elements that matches it instead of just the first? – Intorsion

I'm afraid I am using Mongo 3.0.X :-( – Ironclad

@Ironclad Then use one of the other answers that use aggregate. – Boulevard

this query only return the array "shapes" and it won't return other fields. Anyone knows how to return other fields also? – Mindful

This also works: db.test.find({}, {shapes: {$elemMatch: {color: "red"}}}); – Constellation

@johnnyhk How to use it with array, suppose I have to return shapes where color matches to ["red", "green", "blue"] ? – Vacate

@user3542450 Best to post that as a new question rather than discuss it here. – Boulevard

@Vacate i have answered your query down,please take a look.Thank You – Spelter

Is this an error: $$shape.color? the double $$ in the condition of the $filter. – Callicrates

The "$$" is normal. Checkout the $filter documentation (link above) – Cayuga

Is there a way to achieve the same result but using MongoRepository of Spring Data Mongo? – Thorax

I wanted to keep every shapes and the original document, this is the aggregation I end with. – Nasya

Answer 2

The new Aggregation Framework in MongoDB 2.2+ provides an alternative to Map/Reduce. The $unwind operator can be used to separate your shapes array into a stream of documents that can be matched:

db.test.aggregate(
  // Start with a $match pipeline which can take advantage of an index and limit documents processed
  { $match : {
     "shapes.color": "red"
  }},
  { $unwind : "$shapes" },
  { $match : {
     "shapes.color": "red"
  }}
)

Results in:

{
    "result" : [
        {
            "_id" : ObjectId("504425059b7c9fa7ec92beec"),
            "shapes" : {
                "shape" : "circle",
                "color" : "red"
            }
        }
    ],
    "ok" : 1
}

Habited

@JohnnyHK: In this case, $elemMatch is another option. I actually got here by way of a Google Group question where $elemMatch wouldn't work because it only returns the first match per document. – Habited

Thanks, I wasn't aware of that limitation so that's good to know. Sorry for deleting my comment you're responding to, I decided to post another answer instead and didn't want to confuse people. – Boulevard

@JohnnyHK: No worries, there are now three useful answers for the question ;-) – Habited

For other searchers, in addition to this I also tried adding { $project : { shapes : 1 } } - which seemed to work and would be helpful if the enclosing documents were large and you just wanted to view the shapes key values. – Batey

aggregate $undiwnd shapes at the start of the aggregation is crazy high costs in larg databases, very very not recomended – Phalarope

@calmbird You are correct; this is a simplified example. For a large data set you'd want to start with an indexed $match query or use $elemMatch with a normal find() if the first matching element suffices. – Habited

@calmbird I updated the example to include an initial $match stage. If you're interested in a more efficient feature suggestion I would watch/upvote SERVER-6612: Support projecting multiple array values in a projection like the $elemMatch projection specifier in the MongoDB issue tracker. – Habited

Thats great! Thanx. I've just asked a question about it, but delleted :P – Phalarope

Answer 3

Another interesing way is to use $redact, which is one of the new aggregation features of MongoDB 2.6. If you are using 2.6, you don't need an $unwind which might cause you performance problems if you have large arrays.

db.test.aggregate([
    { $match: { 
         shapes: { $elemMatch: {color: "red"} } 
    }},
    { $redact : {
         $cond: {
             if: { $or : [{ $eq: ["$color","red"] }, { $not : "$color" }]},
             then: "$$DESCEND",
             else: "$$PRUNE"
         }
    }}]);

$redact "restricts the contents of the documents based on information stored in the documents themselves". So it will run only inside of the document. It basically scans your document top to the bottom, and checks if it matches with your if condition which is in $cond, if there is match it will either keep the content($$DESCEND) or remove($$PRUNE).

In the example above, first $match returns the whole shapes array, and $redact strips it down to the expected result.

Note that {$not:"$color"} is necessary, because it will scan the top document as well, and if $redact does not find a color field on the top level this will return false that might strip the whole document which we don't want.

Adulteress

perfect answer. As you mentioned $unwind will consume lot of RAM. So this will be better when compared. – Leesaleese

I have a doubt. In the example, "shapes" is an array. Will "$redact" scan all the objects in the "shapes" array ?? How this will be good with respect to performance?? – Leesaleese

not all of it, but the result of your first match. That is the reason why you put $match as your first aggregate stage – Adulteress

okkk.. if an index created on "color" field, even then it will scan all the objects in the "shapes" array??? Which could be the efficient way of matching multiple objects in an array??? – Leesaleese

@Adulteress if I get this right, there is no way to use the index defined on the array field in order speed up the retrievial of matching array elements: both $unwind and $redact have to scan the whole array, but $redact uses way less ram, because it works on the single document instead of creating one "document copy" per array element. Am I right or am I missing something? – Chantress

Brilliant! I do not understand how $eq works here. I left it off originally and this didn't work for me. Somehow, it looks in the array of shapes to find the match, but the query never specifies which array to look in. Like, if the documents had shapes and, for example, sizes; would $eq look in both arrays for matches? Is $redact just looking for anything within the document that matches the 'if' condition? – Forenamed

Answer 4

Caution: This answer provides a solution that was relevant at that time, before the new features of MongoDB 2.2 and up were introduced. See the other answers if you are using a more recent version of MongoDB.

The field selector parameter is limited to complete properties. It cannot be used to select part of an array, only the entire array. I tried using the $ positional operator, but that didn't work.

The easiest way is to just filter the shapes in the client.

If you really need the correct output directly from MongoDB, you can use a map-reduce to filter the shapes.

function map() {
  filteredShapes = [];

  this.shapes.forEach(function (s) {
    if (s.color === "red") {
      filteredShapes.push(s);
    }
  });

  emit(this._id, { shapes: filteredShapes });
}

function reduce(key, values) {
  return values[0];
}

res = db.test.mapReduce(map, reduce, { query: { "shapes.color": "red" } })

db[res.result].find()

Cognizance

Answer 5

Better you can query in matching array element using $slice is it helpful to returning the significant object in an array.

db.test.find({"shapes.color" : "blue"}, {"shapes.$" : 1})

$slice is helpful when you know the index of the element, but sometimes you want whichever array element matched your criteria. You can return the matching element with the $ operator.

Laity

Does it return all documents that contain shapes.color : blue or just the first one? – Pulsation

@Pulsation this will return only one. – Kelly

Answer 6

 db.getCollection('aj').find({"shapes.color":"red"},{"shapes.$":1})

OUTPUTS

{

   "shapes" : [ 
       {
           "shape" : "circle",
           "color" : "red"
       }
   ]
}

Snaggy

thanks for the query, but it is just returning the first one even though the condition is matching for multiple elements in the array, any suggestion? – Beetner

Answer 7

The syntax for find in mongodb is

    db.<collection name>.find(query, projection);

and the second query that you have written, that is

    db.test.find(
    {shapes: {"$elemMatch": {color: "red"}}}, 
    {"shapes.color":1})

in this you have used the $elemMatch operator in query part, whereas if you use this operator in the projection part then you will get the desired result. You can write down your query as

     db.users.find(
     {"shapes.color":"red"},
     {_id:0, shapes: {$elemMatch : {color: "red"}}})

This will give you the desired result.

Rheta

This works for me. However, It appears that "shapes.color":"red" in the query parameter (the first parameter of the find method) is not necessary. You can replace it with {} and get the same results. – Putnem

@ErikOlson Your suggestion is right in the above case, where we need to find all the document that with red color and to apply the projection on them only. But let's say if somebody requires to find out all the document that have color blue but it should return only those element of that shapes array that have color red. In this case the above query can be referenced by somebody else also.. – Rheta

This seems to be the easiest, but I can't make it work it. It only returns the first matching subdocument. – Fantasy

@MahmoodHussain This answer is almost 7 years old, so may be version issue. Can you check latest documentation. I will try to run similar on latest version and share my findings. Can you explain what exactly you are trying to achieve ? – Rheta

@Rheta Patient.find( { user: req.user._id, _id: req.params.patientId, "tests.test": req.params.testId, }, { "tests.$": 1, name: 1, } ) .populate({ path: "tests", populate: { path: "test", model: "Test", }, }) .exec((err, patient) => { if (err || !patient) { return res.status(404).send({ error: { message: err } }); } return res.send({ patient }); }); But then populate is throwing an error – Englishry

Hi @MahmoodHussain, this doesn't look like native mongodb query. Are you using Mongoose or any other library to query mongo. This question was for native mongo query only. Please provide more details about your implementation and probably share object structure. You can create a new question here on stack overflow with all the details, and we can help you better there. – Rheta

@MahmoodHussain One quick thing I can suggest it to add model name after first populate method call, .populate({ path: "tests", <<< ADD MODEL NAME HERE >>>, populate: { path: "test", model: "Test", .... – Rheta

Answer 8

Thanks to JohnnyHK.

Here I just want to add some more complex usage.

// Document 
{ 
"_id" : 1
"shapes" : [
  {"shape" : "square",  "color" : "red"},
  {"shape" : "circle",  "color" : "green"}
  ] 
} 

{ 
"_id" : 2
"shapes" : [
  {"shape" : "square",  "color" : "red"},
  {"shape" : "circle",  "color" : "green"}
  ] 
} 


// The Query   
db.contents.find({
    "_id" : ObjectId(1),
    "shapes.color":"red"
},{
    "_id": 0,
    "shapes" :{
       "$elemMatch":{
           "color" : "red"
       } 
    }
}) 


//And the Result

{"shapes":[
    {
       "shape" : "square",
       "color" : "red"
    }
]}

Niggle

Answer 9

You just need to run query

db.test.find(
{"shapes.color": "red"}, 
{shapes: {$elemMatch: {color: "red"}}});

output of this query is

{
    "_id" : ObjectId("562e7c594c12942f08fe4192"),
    "shapes" : [ 
        {"shape" : "circle", "color" : "red"}
    ]
}

as you expected it'll gives the exact field from array that matches color:'red'.

Concordant

Answer 10

Along with $project it will be more appropriate other wise matching elements will be clubbed together with other elements in document.

db.test.aggregate(
  { "$unwind" : "$shapes" },
  { "$match" : { "shapes.color": "red" } },
  { 
    "$project": {
      "_id":1,
      "item":1
    }
  }
)

Anesthetic

can you pls describe that this accomplishes with an input and output set? – Untwist

Answer 11

Likewise you can find for the multiple

db.getCollection('localData').aggregate([
    // Get just the docs that contain a shapes element where color is 'red'
  {$match: {'shapes.color': {$in : ['red','yellow'] } }},
  {$project: {
     shapes: {$filter: {
        input: '$shapes',
        as: 'shape',
        cond: {$in: ['$$shape.color', ['red', 'yellow']]}
     }}
  }}
])

Spelter

This answer is indeed the prefered 4.x way: $match to cut down the space, then $filter to keep what you want, overwriting the input field (use output of $filter on field shapes to $project back on to shapes. Style note: best not to use the field name as the as argument because that can lead to confusion later with $$shape and $shape. I prefer zz as the as field because it really stands out. – Dennisedennison

Answer 12

Although the question was asked 9.6 years ago, this has been of immense help to numerous people, me being one of them. Thank you everyone for all your queries, hints and answers. Picking up from one of the answers here.. I found that the following method can also be used to project other fields in the parent document.This may be helpful to someone.

For the following document, the need was to find out if an employee (emp #7839) has his leave history set for the year 2020. Leave history is implemented as an embedded document within the parent Employee document.

db.employees.find( {"leave_history.calendar_year": 2020}, 
    {leave_history: {$elemMatch: {calendar_year: 2020}},empno:true,ename:true}).pretty()


{
        "_id" : ObjectId("5e907ad23997181dde06e8fc"),
        "empno" : 7839,
        "ename" : "KING",
        "mgrno" : 0,
        "hiredate" : "1990-05-09",
        "sal" : 100000,
        "deptno" : {
                "_id" : ObjectId("5e9065f53997181dde06e8f8")
        },
        "username" : "none",
        "password" : "none",
        "is_admin" : "N",
        "is_approver" : "Y",
        "is_manager" : "Y",
        "user_role" : "AP",
        "admin_approval_received" : "Y",
        "active" : "Y",
        "created_date" : "2020-04-10",
        "updated_date" : "2020-04-10",
        "application_usage_log" : [
                {
                        "logged_in_as" : "AP",
                        "log_in_date" : "2020-04-10"
                },
                {
                        "logged_in_as" : "EM",
                        "log_in_date" : ISODate("2020-04-16T07:28:11.959Z")
                }
        ],
        "leave_history" : [
                {
                        "calendar_year" : 2020,
                        "pl_used" : 0,
                        "cl_used" : 0,
                        "sl_used" : 0
                },
                {
                        "calendar_year" : 2021,
                        "pl_used" : 0,
                        "cl_used" : 0,
                        "sl_used" : 0
                }
        ]
}

Rufena

Answer 13

db.test.find( {"shapes.color": "red"}, {_id: 0})

Spaniel

Welcome to Stack Overflow! Thank you for the code snippet, which might provide some limited, immediate help. A proper explanation would greatly improve its long-term value by describing why this is a good solution to the problem, and would make it more useful to future readers with other similar questions. Please edit your answer to add some explanation, including the assumptions you've made. – Torrid

Answer 14

Use aggregation function and $project to get specific object field in document

db.getCollection('geolocations').aggregate([ { $project : { geolocation : 1} } ])

result:

{
    "_id" : ObjectId("5e3ee15968879c0d5942464b"),
    "geolocation" : [ 
        {
            "_id" : ObjectId("5e3ee3ee68879c0d5942465e"),
            "latitude" : 12.9718313,
            "longitude" : 77.593551,
            "country" : "India",
            "city" : "Chennai",
            "zipcode" : "560001",
            "streetName" : "Sidney Road",
            "countryCode" : "in",
            "ip" : "116.75.115.248",
            "date" : ISODate("2020-02-08T16:38:06.584Z")
        }
    ]
}

Goethite

Answer 15

if you want to do filter, set and find at the same time.

let post = await Post.findOneAndUpdate(
          {
            _id: req.params.id,
            tasks: {
              $elemMatch: {
                id: req.params.jobId,
                date,
              },
            },
          },
          {
            $set: {
              'jobs.$[i].performer': performer,
              'jobs.$[i].status': status,
              'jobs.$[i].type': type,
            },
          },
          {
            arrayFilters: [
              {
                'i.id': req.params.jobId,
              },
            ],
            new: true,
          }
        );

Streit

Answer 16

This answer does not fully answer the question but it's related and I'm writing it down because someone decided to close another question marking this one as duplicate (which is not).

In my case I only wanted to filter the array elements but still return the full elements of the array. All previous answers (including the solution given in the question) gave me headaches when applying them to my particular case because:

• I needed my solution to be able to return multiple results of the subarray elements.
• Using $unwind + $match + $group resulted in losing root documents without matching array elements, which I didn't want to in my case because in fact I was only looking to filter out unwanted elements.
• Using $project > $filter resulted in loosing the rest of the fields or the root documents or forced me to specify all of them in the projection as well which was not desirable.

So at the end I fixed all of this problems with an $addFields > $filter like this:

db.test.aggregate([
    { $match: { 'shapes.color': 'red' } },
    { $addFields: { 'shapes': { $filter: {
      input: '$shapes',
      as: 'shape',
      cond: { $eq: ['$$shape.color', 'red'] }
    } } } },
])

Explanation:

1. First match documents with a red coloured shape.
2. For those documents, add a field called shapes, which in this case will replace the original field called the same way.
3. To calculate the new value of shapes, $filter the elements of the original $shapes array, temporarily naming each of the array elements as shape so that later we can check if the $$shape.color is red.
4. Now the new shapes array only contains the desired elements.

Dactylology

Answer 17

For the new version of MongoDB, it's slightly different.

For db.collection.find you can use the second parameter of find with the key being projection

db.collection.find({}, {projection: {name: 1, email: 0}});

You can also use the .project() method.
However, it is not a native MongoDB method, it's a method provided by most MongoDB driver like Mongoose, MongoDB Node.js driver etc.

db.collection.find({}).project({name: 1, email: 0});

And if you want to use findOne, it's the same that with find

db.collection.findOne({}, {projection: {name: 1, email: 0}});

But findOne doesn't have a .project() method.

Cocks

Answer 18

for more details refer =

mongo db official referance

suppose you have document like this (you can have multiple document too) - 

{
  "_id": {
    "$oid": "63b5cfbfbcc3196a2a23c44b"
  },
  "results": [
    {
      "yearOfRelease": "2022",
      "imagePath": "https://upload.wikimedia.org/wikipedia/en/d/d4/The_Kashmir_Files_poster.jpg",
      "title": "The Kashmir Files",
      "overview": "Krishna endeavours to uncover the reason behind his parents' brutal killings in Kashmir. He is shocked to uncover a web of lies and conspiracies in connection with the massive genocide.",
      "originalLanguage": "hi",
      "imdbRating": "8.3",
      "isbookMark": null,
      "originCountry": "india",
      "productionHouse": [
        "Zee Studios"
      ],
      "_id": {
        "$oid": "63b5cfbfbcc3196a2a23c44c"
      }
    },
    {
      "yearOfRelease": "2022",
      "imagePath": "https://upload.wikimedia.org/wikipedia/en/a/a9/Black_Adam_%28film%29_poster.jpg",
      "title": "Black Adam",
      "overview": "In ancient Kahndaq, Teth Adam was bestowed the almighty powers of the gods. After using these powers for vengeance, he was imprisoned, becoming Black Adam. Nearly 5,000 years have passed, and Black Adam has gone from man to myth to legend. Now free, his unique form of justice, born out of rage, is challenged by modern-day heroes who form the Justice Society: Hawkman, Dr. Fate, Atom Smasher and Cyclone",
      "originalLanguage": "en",
      "imdbRating": "8.3",
      "isbookMark": null,
      "originCountry": "United States of America",
      "productionHouse": [
        "DC Comics"
      ],
      "_id": {
        "$oid": "63b5cfbfbcc3196a2a23c44d"
      }
    },
    {
      "yearOfRelease": "2022",
      "imagePath": "https://upload.wikimedia.org/wikipedia/en/0/09/The_Sea_Beast_film_poster.png",
      "title": "The Sea Beast",
      "overview": "A young girl stows away on the ship of a legendary sea monster hunter, turning his life upside down as they venture into uncharted waters.",
      "originalLanguage": "en",
      "imdbRating": "7.1",
      "isbookMark": null,
      "originCountry": "United States Canada",
      "productionHouse": [
        "Netflix Animation"
      ],
      "_id": {
        "$oid": "63b5cfbfbcc3196a2a23c44e"
      }
    },
    {
      "yearOfRelease": "2021",
      "imagePath": "https://upload.wikimedia.org/wikipedia/en/7/7d/Hum_Do_Hamare_Do_poster.jpg",
      "title": "Hum Do Hamare Do",
      "overview": "Dhruv, who grew up an orphan, is in love with a woman who wishes to marry someone with a family. In order to fulfil his lover's wish, he hires two older individuals to pose as his parents.",
      "originalLanguage": "hi",
      "imdbRating": "6.0",
      "isbookMark": null,
      "originCountry": "india",
      "productionHouse": [
        "Maddock Films"
      ],
      "_id": {
        "$oid": "63b5cfbfbcc3196a2a23c44f"
      }
    },
    {
      "yearOfRelease": "2021",
      "imagePath": "https://upload.wikimedia.org/wikipedia/en/7/74/Shang-Chi_and_the_Legend_of_the_Ten_Rings_poster.jpeg",
      "title": "Shang-Chi and the Legend of the Ten Rings",
      "overview": "Shang-Chi, a martial artist, lives a quiet life after he leaves his father and the shadowy Ten Rings organisation behind. Years later, he is forced to confront his past when the Ten Rings attack him.",
      "originalLanguage": "en",
      "imdbRating": "7.4",
      "isbookMark": null,
      "originCountry": "United States of America",
      "productionHouse": [
        "Marvel Entertainment"
      ],
      "_id": {
        "$oid": "63b5cfbfbcc3196a2a23c450"
      }
    }
  ],
  "__v": 0
}



=======


mongo db query by aggregate command - 


mongomodels.movieMainPageSchema.aggregate(
        [
            {
               $project: {
                _id:0,  // to supress id
                results: {
                     $filter: {
                        input: "$results",
                        as: "result",
                        cond: { $eq: [ "$$result.yearOfRelease", "2022" ] }
                     }
                  }
               }
            }
         ]

    )
    
    
    

Mariehamn

Answer 19

If you want to return the first matching shapes, find() will do the trick (as detailed here), but if you want to return every matching shapes with the original document you can do the following aggregate:

db.collection.aggregate([
  {
    $set: {
      shapes: {
        $filter: {
          input: "$shapes",
          as: "shape",
          cond: {$eq: ["$$shape.color", "red"]}
        }
      }
    }
  },
  {
    $match: {
      $nor: [
        {shapes: {$exists: false}},
        {shapes: {$size: 0}}
      ]
    }
  }
])

Play with it in mongo playground. This is filtering the shapes attribute in place and keeping the others (instead of using $project which remove others attributes, and need an extra step to get the doc back).

The additional $match is optionnal, it removes documents with empty shapes array.

Nasya

Answer 20

    db.collection.aggregate([
      {
        "$unwind": "$shapes"
      },
      {
        "$match": {
          "$and": [
            {
              "shapes.color": "red"
            },
            {
              "shapes.shape": "circle"
            }
          ]
        }
      },
      //remove objectid
      {
        "$project": {
          _id: 0
        }
      }
    ])

Unamerican