By chenka


2013-02-27 16:21:09 8 Comments

I have array in subdocument like this

{
    "_id" : ObjectId("512e28984815cbfcb21646a7"),
    "list" : [
        {
            "a" : 1
        },
        {
            "a" : 2
        },
        {
            "a" : 3
        },
        {
            "a" : 4
        },
        {
            "a" : 5
        }
    ]
}

Can I filter subdocument for a > 3

My expect result below

{
    "_id" : ObjectId("512e28984815cbfcb21646a7"),
    "list" : [
        {
            "a" : 4
        },
        {
            "a" : 5
        }
    ]
}

I try to use $elemMatch but returns the first matching element in the array

My query:

db.test.find( { _id" : ObjectId("512e28984815cbfcb21646a7") }, { 
    list: { 
        $elemMatch: 
            { a: { $gt:3 } 
            } 
    } 
} )

The result return one element in array

{ "_id" : ObjectId("512e28984815cbfcb21646a7"), "list" : [ { "a" : 4 } ] }

and I try to use aggregate with $match but not work

db.test.aggregate({$match:{_id:ObjectId("512e28984815cbfcb21646a7"), 'list.a':{$gte:5}  }})

It's return all element in array

{
    "_id" : ObjectId("512e28984815cbfcb21646a7"),
    "list" : [
        {
            "a" : 1
        },
        {
            "a" : 2
        },
        {
            "a" : 3
        },
        {
            "a" : 4
        },
        {
            "a" : 5
        }
    ]
}

Can I filter element in array to get result as expect result?

3 comments

@JohnnyHK 2013-02-27 17:04:51

Using aggregate is the right approach, but you need to $unwind the list array before applying the $match so that you can filter individual elements and then use $group to put it back together:

db.test.aggregate([
    { $match: {_id: ObjectId("512e28984815cbfcb21646a7")}},
    { $unwind: '$list'},
    { $match: {'list.a': {$gt: 3}}},
    { $group: {_id: '$_id', list: {$push: '$list.a'}}}
])

outputs:

{
  "result": [
    {
      "_id": ObjectId("512e28984815cbfcb21646a7"),
      "list": [
        4,
        5
      ]
    }
  ],
  "ok": 1
}

MongoDB 3.2 Update

Starting with the 3.2 release, you can use the new $filter aggregation operator to do this more efficiently by only including the list elements you want during a $project:

db.test.aggregate([
    { $match: {_id: ObjectId("512e28984815cbfcb21646a7")}},
    { $project: {
        list: {$filter: {
            input: '$list',
            as: 'item',
            cond: {$gt: ['$$item.a', 3]}
        }}
    }}
])

@Cherif 2013-10-27 22:18:51

Does the aggregation operation modify the document or does it just perform a selection ?

@JohnnyHK 2013-10-27 23:40:13

@Cherif aggregate doesn't affect the document itself.

@samson 2017-03-07 23:01:35

How would you go about it, If you wanted to publish the result of that query to the client?

@Rahul 2017-03-18 15:48:42

Use $filter aggregation

Selects a subset of the array to return based on the specified condition. Returns an array with only those elements that match the condition. The returned elements are in the original order.

db.test.aggregate([
    {$match: {"list.a": {$gt:3}}}, // <-- match only the document which have a matching element
    {$project: {
        list: {$filter: {
            input: "$list",
            as: "list",
            cond: {$gt: ["$$list.a", 3]} //<-- filter sub-array based on condition
        }}
    }}
]);

@keshav 2015-12-03 11:07:22

Above solution works best if multiple matching sub documents are required. $elemMatch also comes in very use if single matching sub document is required as output

db.test.find({list: {$elemMatch: {a: 1}}}, {'list.$': 1})

Result:

{
  "_id": ObjectId("..."),
  "list": [{a: 1}]
}

@Ulises Layera 2018-03-08 14:23:53

This was the best solution for me. But the second argument works only with primitive types. As i had an object in the array, my solution was: ShoppingCartModel.findOne({"_id": ctx.params.idCart, "active" : true, products: {$elemMatch : {"products.active" : true}}}) (I was filtering only the active: true carts and products)

Related Questions

Sponsored Content

14 Answered Questions

[SOLVED] How to Update Multiple Array Elements in mongodb

11 Answered Questions

[SOLVED] Query for documents where array size is greater than 1

11 Answered Questions

34 Answered Questions

[SOLVED] How to query MongoDB with "like"?

7 Answered Questions

[SOLVED] How to design RESTful search/filtering?

19 Answered Questions

[SOLVED] How do I perform the SQL Join equivalent in MongoDB?

  • 2010-02-28 08:15:42
  • The Unknown
  • 309217 View
  • 461 Score
  • 19 Answer
  • Tags:   mongodb join

1 Answered Questions

[SOLVED] Filtering an array, in a subdocument, in an array, in a document in Mongoose/MongoDB

  • 2017-08-17 15:08:43
  • AmateurAardvark
  • 826 View
  • 2 Score
  • 1 Answer
  • Tags:   mongodb mongoose

1 Answered Questions

[SOLVED] MongoDB find subdocument and sort the results

1 Answered Questions

6 Answered Questions

[SOLVED] Bulk insert from Array in mongodb JavaScript console

Sponsored Content