Get all results from array that matches property

Question

I know how to make MongoDB find a row based on an array like this:

useritems.find({userid: useridhere,  "items.id": idhere})

But how would I for example search and get all items that are activated, or get all items based on an items property? Like for example:

useritems.find({userid: useridhere,  "items.activated": true})

Would results in getting all items from the user where activated is true.

Here is my items schema:

var userItemsSchema = new Schema({
    userid : String,
    items: [
        {
            id: {type: Number, default: 1},
            activated: { type: Boolean, default: false},
            endtime : {type: Number, default: 0},
        },
    ],
});

module.exports = mongoose.model('useritems', userItemsSchema);

This has already been asked, and answered well; see https://stackoverflow.com/q/3985214/174843. — Vince Bowdren, Jul 25 '17 at 10:26
@VinceBowdren We know. But it cannot be closed as duplicate until the bounty period expires. Sometimes questions slip through — Neil Lunn, Jul 27 '17 at 02:57

score 4 · Answer 1 · answered Jul 20 '17 at 06:41

You want $filter here:

useritems.aggregate([
  { "$match": { 
    "userid": ObjectId(useridhere),  
    "items.activated": true
  }},
  { "$addFields": {
    "items": {
      "$filter": {
        "input": "$items",
        "as": "i",
        "cond": "$$i.activated"
      }
    }
  }}
],(err,results) => { 

});

Noting that with the aggregation framework such values as useridhere which mongoose normally allows you to pass in a "string" for and would "autocast" that string into an ObjectId value for you. This does not happen in the aggregation framework^Issue#1399, simply because since it can possibly change the "shape" of the documents acted on, then no "schema" can be applied.

So you likely want to import this from the core driver instead:

const ObjectId = require('mongodb').ObjectID;

Then you can "cast" the values manually.

Of course if such a value is actually retrieved from another mongoose object rather than from req.params or similar, then it already should be of an ObjectId type.

The reason why you use .aggregate() for this is because "standard projection" only ever matches one element. i.e:

useritems.find({ userid: useridhere,  "items.activated": true })
 .select('items.$')
 .exec((err,items) => {

 });

Here the positional $ operator returns the "matched" element, but only ever the "first" match.

So where you want "multiple" matches you use $filter instead, and that is a lot more effective than earlier versions of MongoDB which require you to $unwind the array first.

The $unwind operator should only be used in modern releases ( Anything past MongoDB 2.4 ) in the case where you actually want to use a "value" from within an array for an operation such as $group where that value is presented as a "grouping key". It's usage in other cases is generally a "huge performance problem", with the exception of directly following a $lookup pipeline stage where it does have a special important use case.

Otherwise best avoided though. Use $filter instead.

NOTE: The $addFields pipeline stage allows you to "overwrite" a single element without specifying all the other fields. If your MongoDB does not support this operator, use $project instead and specify all fields explicitly. i.e:

  { "$project": {
    "userid": 1,
    "items": {
      "$filter": {
        "input": "$items",
        "as": "i",
        "cond": "$$i.activated"
      }
    }
  }}

score 1 · Answer 2 · answered Jul 20 '17 at 02:51

It may be a good choice to use aggregate like:

useritems.aggregate(
    { $match: {"_id" : id}},
    { $unwind: '$items'},
    { $match: {'items.activated': true}},
    { $group: {_id: '$_id', items: {$push: '$items'}}})

You can get further info in this question: How to filter array in subdocument with MongoDB

score -1 · Answer 3 · answered Jul 23 '17 at 07:15

-1

$elemMatch operator is used to query a value in an embedded document

According to description as mentioned in above question, as a solution to it please try executing following find operation in MongoDB shell.

    db.useritems.find({
    userid: "random user_id",
    items: {
        $elemMatch: {
            activated: true
        }
    }
 })

answered Jul 23 '17 at 07:15

Rubin Porwal

3,736
1
23
26

1

Question says: *"get **all** items that are activated,"*. This cannot be done with standard [positional projection](https://docs.mongodb.com/manual/reference/operator/projection/positional/) since it only ever returns the **first** response of a match. But what you are saying is completely incorrect. 1. `$elemMatch` is only ever needed when there are "multiple" query conditions to match the array element, and this only needs **one** condition. 2. Without the positional `$` operator **all** array items return anyway. The question asks for the "matched items only". – Neil Lunn Jul 23 '17 at 22:59

Get all results from array that matches property

3 Answers3