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

User · Answer

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

User · Answer

The syntax for find in mongodb is       db  lt collection name gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

db test find    shapes color    red      id  0

User · Answer

db getCollection  aj   find   shapes color   red     shapes    1     OUTPUTS         shapes                            shape     circle               color     red

User · Answer

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

User · Answer

Along with   project it will be more appropriate  other wise matching elements will be clubbed together with other elements in document  db test aggregate       quot  unwind quot     quot  shapes quot          quot  match quot       quot shapes color quot    quot red quot                 quot  project quot            quot  id quot  1         quot item quot  1

User · Answer

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

User · Answer

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

[mongodb] Retrieve only the queried element in an object array in MongoDB collection

Examples related to mongodb

Examples related to mongodb-query

Examples related to aggregation-framework

Examples related to projection