Return Array Documents within a Date Range

Question

Is it possible to chain finById(), select() and find() in mongoose? I am trying something like this and it doesn't accept the date, instead just returns all objects in familyStats collection.

module.exports.getFamilyStatsPast = function (req, res) {
    var id = req.params.id;
    var days = req.params.days;

    var pastDate = new Date();
    pastDate.setDate(pastDate.getDate() - days).toISOString();
    var currentDate = new Date().toISOString();

    Worksets
        .findById(id)
        .select('familyStats')
        .find({
            createdOn : {
                $gte: pastDate,
                $lte: currentDate
            }
        })
        .exec(function (err, doc){
            var response = {
                status: 200,
                message: []
            };
            if (err){
                console.log("Error finding Workset");
                response.status = 500;
                response.message = err;
            } else if(!doc){
                console.log("Workset id not found in database", id);
                response.status = 404;
                response.message = {"message": "Workset Id not found."}
            }
            if(doc){
                console.log("Found workset: ", doc);
                res
                    .status(response.status)
                    .json(doc)
            } else {
                res
                    .status(response.status)
                    .json(response.message);
            }
        });
};

I am basically trying to get - from Worksets collection an array called familyStats - then from that array I want just objects that were created between pastDate and currentDate which is from last week until now.

Any ideas what I am doing wrong?

Here's my mongoose data schema:

var familyStatsSchema = new mongoose.Schema({
    suspectFamilies: [{
        name: String,
        size: String,
        sizeValue: Number,
        instances: Number,
        elementId: Number
    }],
    totalFamilies: Number,
    unusedFamilies: Number,
    oversizedFamilies: Number,
    inPlaceFamilies: Number,
    createdBy: String,
    createdOn: Date
});

var healthCheckSchema = new mongoose.Schema({
    onOpened: [worksetEventSchema],
    onSynched: [worksetEventSchema],
    itemCount : [worksetItemSchema],
    viewStats: [viewStatsSchema],
    linkStats: [linksStatsSchema],
    familyStats: [familyStatsSchema]
});

mongoose.model('Worksets', healthCheckSchema);

Answer 1

If all you want is to get data from within a certain date range from an array in the document, all you need is the $filter operation from the aggregation framework:

var id = req.params.id;
var days = parseInt(req.params.days);

var now = new Date(),
    firstDate = new Date(now.valueOf() - ( 1000 * 60 * 60 * 24 * days ));

Worksets.aggregate([
  { "$match": { 
    "_id": ObjectId(id),
    "familyStats": { 
      { "$elemMatch": { "createdOn": { "$gte": firstDate, "$lt": now } } }
  }},
  { "$project": {
    "familtStats": {
      "$filter": {
        "input": "$familyStats",
        "as": "el",
        "cond": { "$and": [
          { "$gte": [ "$$el.createdOn", firstDate ] },
          { "$lt": [ "$$el.createdOn", now ] }
        ]}
      }
    }
  }}
],function(err, results) {
  // work with response
})

There is an initial $match condition that works just the same as with .find() where we specify the query conditions. I'm adding the date range conditions with an $elemMatch since there is no point returning the document at all if there are no dates within the range $elemMatch since the "range expression" is actually an AND condition applied to the array elements. Without it, you are simply asking if the whole array meets those conditions rather than "if something falls between them".

Also to note here is the ObjectId constructor. Mongoose "autocasts" values for the _id field from "strings" normally, but with the aggregation framework it cannot do this on it's own. You need to do the "cast" manually. So import from Schema.Types and apply it to the parameter received here.

After the document(s) ( possibly without including the _id constraint ) have been matched, the $project pipeline stage determines the "shape" of returned results. Like standard query projection you must include "all" fields you actually want here. For example purposes I am just returning the array in question.

The $filter applies the same sort of logic conditions as were present in the $match, except this time they are actually applied to the "array elements themselves" as opposed to whether the "document(s)" match the conditions. Anything that does not meet the criteria given is removed from the array which is returned.

That will give you an operation with approximately the same performance as a regular .find() query, except the operators used are able to remove the unwanted content outside of the date range from the array "before" the results are returned from the server.

With large documents like yours, this should be a regular operation you perform with respect to other arrays present as well.