mongodbfiltermongodb-queryaggregation-framework

How to filter array in subdocument with MongoDB


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

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