Categories
aggregation-framework mongodb mongodb-query projection

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

459

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?

0

    502

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

    10

    • 26

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

      – Steve Ng

      Dec 25, 2013 at 8:12

    • I’m afraid I am using Mongo 3.0.X 🙁

      Jan 10, 2016 at 20:33


    • @charliebrownie Then use one of the other answers that use aggregate.

      – JohnnyHK

      Jan 10, 2016 at 21:14

    • 1

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

      – Paul

      Jul 14, 2017 at 19:15

    • 1

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

      May 17, 2021 at 21:17


    114

    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
    }
    

    8

    • 7

      @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.

      – Stennie

      Sep 3, 2012 at 4:24

    • 1

      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.

      – JohnnyHK

      Sep 3, 2012 at 4:35


    • 3

      @JohnnyHK: No worries, there are now three useful answers for the question 😉

      – Stennie

      Sep 3, 2012 at 4:41

    • 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.

      Dec 4, 2014 at 8:23


    • 2

      @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.

      – Stennie

      Jan 28, 2015 at 8:52

    34

    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()
    

    0