How to filter array in subdocument with MongoDB [duplicate]

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?

Solution 1:

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

Solution 2:

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

Solution 3:

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