How to filter array in subdocument with MongoDB [duplicate]

Question

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?

JohnnyHK · Accepted Answer · 2023-07-20 03:28:14Z

219

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

edited Jul 20, 2023 at 3:28

answered Feb 27, 2013 at 17:04

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Cherif Over a year ago

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

JohnnyHK Over a year ago

@Cherif aggregate doesn't affect the document itself.

samson Over a year ago

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

kartik Over a year ago

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.

Cache Over a year ago

@JohnnyHK There is typo in the last condition(closed parantheses)

|

keshav · Accepted Answer · 2017-02-27 05:32:19Z

45

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

edited Feb 27, 2017 at 5:32

answered Dec 3, 2015 at 11:07

keshav

7526 silver badges19 bronze badges

2 Comments

Ulises Layera Over a year ago

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)

Nate Anderson Over a year ago

Nice solution, avoids the aggregate(...) required here (even though I love aggregate and I think the aggregate approach is a great answer)

Rahul · Accepted Answer · 2017-03-18 15:48:42Z

28

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

answered Mar 18, 2017 at 15:48

Rahul

16.4k4 gold badges44 silver badges64 bronze badges

2 Comments

Seth Falco Over a year ago

Note that if you're using Atlas, $filter cannot be used on the free-tier.

cprcrack Over a year ago

@SethFalco that doesn't seem to be the case anymore in Oct 2022

Collectives™ on Stack Overflow

How to filter array in subdocument with MongoDB [duplicate]

3 Answers 3

8 Comments

2 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

2 Comments

2 Comments

Linked

Related