136

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?

Mani
  • 1,471
  • 1
  • 13
  • 19
chenka
  • 1,463
  • 2
  • 10
  • 5

3 Answers3

212

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

$and: get data between 0-5:

cond: { 
    $and: [
        { $gt: [ "$$item.a", 0 ] },
        { $lt: [ "$$item.a", 5 ] }
]}
JohnnyHK
  • 305,182
  • 66
  • 621
  • 471
  • Does the aggregation operation modify the document or does it just perform a selection ? – Cherif Oct 27 '13 at 22:18
  • 3
    @Cherif `aggregate` doesn't affect the document itself. – JohnnyHK Oct 27 '13 at 23:40
  • 1
    How would you go about it, If you wanted to publish the result of that query to the client? – samson Mar 07 '17 at 23:01
  • 1
    Does this solution utilises the indexes, inside the array elements (if the array element is a document in itself) ? Suppose there is an index on some path in the array element. – kartik Sep 23 '20 at 15:00
  • Use `cond: { $eq: ['$$item.a', ] }` to filter elements based on exact equivalent values. – Raphael Jan 12 '21 at 17:55
  • everywhere I look I see `$filter` references, which is fine for me, but I need to use `$filter` with `$nin` operator and it looks like `cond` does not support it :( does anybody have any workaround for this? – Tamas Nov 23 '21 at 06:23
  • 1
    @JohnnyHK There is typo in the last condition(closed parantheses) – Cache Jul 18 '23 at 15:37
43

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}]
}
keshav
  • 734
  • 6
  • 19
  • 1
    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) – Ulises Layera Mar 08 '18 at 14:23
26

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
        }}
    }}
]);
Rahul
  • 15,979
  • 4
  • 42
  • 63