Get all results from array that matches property [duplicate]

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

Answer 1

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