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?

score 450 · Accepted Answer · edited Sep 25 '18 at 18:04

450

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

edited Sep 25 '18 at 18:04

Faraz

4,939
3
18
57

answered Sep 03 '12 at 04:19

JohnnyHK

270,398
59
567
434

23

any solution if I want it to return every elements that matches it instead of just the first? – Steve Ng Dec 25 '13 at 08:12
I'm afraid I am using **Mongo 3.0.X** :-( – charliebrownie Jan 10 '16 at 20:33
@charliebrownie Then use one of the other answers that use `aggregate`. – JohnnyHK Jan 10 '16 at 21:14
this query only return the array "shapes" and it won't return other fields. Anyone knows how to return other fields also? – Mark Thien Jul 29 '16 at 00:24
1

This also works: `db.test.find({}, {shapes: {$elemMatch: {color: "red"}}});` – Paul Jul 14 '17 at 19:15
@johnnyhk How to use it with array, suppose I have to return shapes where color matches to ["red", "green", "blue"] ? – iit2011081 Oct 13 '17 at 15:40
@user3542450 Best to post that as a new question rather than discuss it here. – JohnnyHK Oct 13 '17 at 16:23
@iit2011081 i have answered your query down,please take a look.Thank You – ashishSober Feb 28 '20 at 07:19
Is this an error: $$shape.color? the double $$ in the condition of the $filter. – Augusto Jara May 17 '21 at 21:17

Stennie · Answer 2 · 2015-01-28T08:41:36.880

106

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
}

edited Jan 28 '15 at 08:41

answered Sep 03 '12 at 03:44

Stennie

57,971
14
135
165

7

@JohnnyHK: In this case, `$elemMatch` is another option. I actually got here by way of a [Google Group question](https://groups.google.com/forum/?fromgroups#!topic/mongodb-user/gASUwWbxN3w) where $elemMatch wouldn't work because it only returns the first match per document. – Stennie Sep 03 '12 at 04: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 03 '12 at 04:35
3

@JohnnyHK: No worries, there are now three useful answers for the question ;-) – Stennie Sep 03 '12 at 04: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. – user1063287 Dec 04 '14 at 08:23
aggregate $undiwnd shapes at the start of the aggregation is crazy high costs in larg databases, very very not recomended – Pikachu Jan 28 '15 at 08:27
@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. – Stennie Jan 28 '15 at 08:31
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](https://jira.mongodb.org/browse/SERVER-6612) in the MongoDB issue tracker. – Stennie Jan 28 '15 at 08:52
Thats great! Thanx. I've just asked a question about it, but delleted :P – Pikachu Jan 28 '15 at 08:57

Niels van der Rest · Answer 3 · 2016-07-19T18:42:45.697

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

score 31 · Answer 4 · answered Jun 04 '14 at 08:31

31

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.

answered Jun 04 '14 at 08:31

anvarik

5,919
5
36
51

1

perfect answer. As you mentioned $unwind will consume lot of RAM. So this will be better when compared. – manojpt Apr 21 '15 at 11:21
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?? – manojpt Apr 23 '15 at 08:13
not all of it, but the result of your first match. That is the reason why you put `$match` as your first aggregate stage – anvarik Apr 23 '15 at 16:36
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???** – manojpt Apr 24 '15 at 04:47
@anvarik 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? – Cec Sep 24 '15 at 12:30
2

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? – Onosa Dec 30 '15 at 14:46

score 27 · Answer 5 · edited Sep 18 '14 at 09:14

27

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.

edited Sep 18 '14 at 09:14

Egor Neliuba

14,144
7
55
71

answered Sep 18 '14 at 08:35

Narendran

615
6
8

score 20 · Answer 6 · answered Dec 07 '16 at 06:25

20

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

OUTPUTS

{

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

answered Dec 07 '16 at 06:25

Viral Patel

753
8
11

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? – Genius Mar 11 '21 at 08:12

score 14 · Answer 7 · edited Jan 31 '14 at 08:19

14

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.

edited Jan 31 '14 at 08:19

Jinxcat

574
1
3
17

answered Dec 22 '13 at 08:14

Vicky

706
6
16

1

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. – Erik Olson May 09 '14 at 20:35
2

@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.. – Vicky May 11 '14 at 09:22
This seems to be the easiest, but I can't make it work it. It only returns the first matching subdocument. – newman Aug 29 '15 at 23:05
populate is not working on this why? – Mahmood Hussain Feb 17 '21 at 07:49
@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 ? – Vicky Feb 17 '21 at 15:59
@Vicky `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 – Mahmood Hussain Feb 18 '21 at 02:32
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. – Vicky Feb 18 '21 at 04:40
@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", .... – Vicky Feb 18 '21 at 04:46

score 8 · Answer 8 · edited May 23 '17 at 11:54

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

score 7 · Answer 9 · edited Nov 10 '16 at 07:50

7

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

edited Nov 10 '16 at 07:50

azhar

1,591
1
16
36

answered Sep 17 '16 at 17:22

Vaibhav Patil

1,427
2
11
21

score 4 · Answer 10 · edited Feb 04 '21 at 17:02

4

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

edited Feb 04 '21 at 17:02

turivishal

21,335
6
16
44

answered Feb 09 '13 at 15:45

shakthydoss

2,341
3
24
34

can you pls describe that this accomplishes with an input and output set? – Alexander Mills Nov 24 '15 at 17:13

score 2 · Answer 11 · answered Feb 28 '20 at 07:15

2

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']]}
     }}
  }}
])

answered Feb 28 '20 at 07:15

ashishSober

981
1
9
23

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. – Buzz Moschetti Mar 03 '20 at 15:52

score 1 · Answer 12 · edited Oct 25 '18 at 08:04

1

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

edited Oct 25 '18 at 08:04

Suraj Rao

28,186
10
88
94

answered Oct 25 '18 at 08:00

Poonam Agrawal

23
2

3

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](https://meta.stackexchange.com/q/114762/206345) 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. – sepehr Oct 25 '18 at 15:06

score 1 · Answer 13 · answered Feb 09 '20 at 07:43

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

score 1 · Answer 14 · answered Apr 16 '20 at 10:07

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

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

14 Answers14

Thanks to JohnnyHK.

Linked

Related