Aggregate from array where date was before today

Question

I am trying to aggregate a collection which has an array. In this array, there's a reminders array. The document might look like this:

{
  _id: "1234",
  dates: {
    start: ISODate(),
    end: ISODate()
  },
  reminders: [{
    sendAt: ISODate(),
    status: 'closed'
  }, {
    sendAt: ISODate(),
    status: 'open'
  }]
}

Say the first one is before today and the next one is after today. What I want to do is get the array of all that come before today, OR, an empty array if none came before today. I tried the following aggregation

db.reminders.aggregate([
  { $match: { 'dates.end': { $gt: new Date } } },
  { $unwind: '$reminders' },
  {
    $match: {
      reminders: {
        $elemMatch: {
          sendAt: { $lt: new Date() },
          status: { $ne: 'open' }
        }
      }
    }
  }
])

However, if there are no reminders before today, it will fail and give nothing back.

Is there a way to construct this structure with mongodb aggregation?

NOTE: I can't use $filter because that is in 3.2

Answer 1

You can use the $redact operator, to filter out sub-documents for versions >=2.6 . It also avoids the unnecessary $unwind stage.

• $match all the documents that have their dates.end attribute greater than the search criteria.
• $redact through all sub-documents and do the following, $$DESCEND into those documents, that match the conditions, else $$PRUNE.

sample code:

var endDateToMatch = ISODate("2014-01-01T00:00:00Z");
var currentDate = ISODate();

db.t.aggregate([
{
  $match:{"dates.end":{$gt:endDateToMatch}}
},
{
  $redact:{$cond:[
                 {$and:[
                        {$ne:[{$ifNull:["$status",""]},
                              "open"]},
                        {$lt:[{$ifNull:["$sendAt",currentDate-1]},
                              currentDate]}
                       ]
                 },
                "$$DESCEND","$$PRUNE"]}
}
])

This would give you one document per document that matches the $match stage. If you need to accumulate all the sub-documents, then you need to $unwind "reminders" and $group by _id as null.

Answer 2

So you basically want $filter behavior but need to do it in an earlier version, with your main case being returning documents even if the array content ends up empty.

For MongoDB 2.6 you can do "almost" the same thing with $map and $setDifference:

db.reminders.aggregate([
    { "$match": { "dates.end": { "$gt": new Date() } } },
    { "$project": {
        "dates": 1,
        "reminders": {
            "$setDifference": [
                { "$map": {
                    "input": "$reminders",
                    "as": "reminder",
                    "in": {
                        "$cond": [
                            { "$and": [
                                { "$lt": [ "$$reminder.sendAt", new Date() ] },
                                { "$ne": [ "$$reminder.status", "open" ] }
                            ]},
                            "$$reminder",
                            false
                        ]
                    }
                }},
                [false]
            ]
        }
    }}
])

And that is okay as long as the resulting "set" from $setDifference is all unqiuely identified items. So the $map method applies the test, either returning the content or false if there was no match to conditions. The $setDifferene essentially removes any false elements from the results, but of course as a "set" would count any items exactly the same as one.

If your MongoDB is less than 2.6 ( or the case of "sets" makes the above unusable), it just requires being a bit more careful when looking at the content to filter:

db.reminders.aggregate([
    { "$match": { "dates.end": { "$gt": new Date() } } },
    { "$unwind": "$reminders" },

    // Count where condition matched
    { "$group": {
        "_id": "$_id",
        "dates": { "$first": "$dates" },
        "reminders": { "$push": "$reminders" },
        "matched": { "$sum": {
            "$cond": [
                { "$and": [
                    { "$lt": [ "$reminders.sendAt", new Date() ] },
                    { "$ne": [ "$reminders.status", "open" ] }
                 ]},
                 1,
                 0
            ]
        }}
    }},

    // Substitute array where no count just for brevity
    { "$project": {
        "dates": 1,
        "reminders": { "$cond": [
            { "$eq": [ "$matched", 0 ] },
            { "$const": [false] },
            "$reminders"
        ]},
        "matched": 1
    }},

    // Unwind again
    { "$unwind": "$reminders" },

    // Filter for matches "or" where there were no matches to keep
    { "$match": {
        "$or": [
            { 
                "reminder.sendAt": { "$lt": new Date() },
                "reminder.status": { "$ne": "open" }
            },
            { "matched": 0 }      
        ]
    }},

    // Group again
    { "$group": {
        "_id": "$_id",
        "dates": { "$first": "$dates" },
        "reminders": { "$push": "$reminders" }
    }},

    // Replace the [false] array with an empty one
    { "$project": {
        "dates": 1,
        "reminders": { "$cond": [
            { "$eq": [ "$reminders", [false] ] },
            { "$const": [] },
            "$reminders"
        ]}
    }}
])

It's a bit long winded, but it's basically doing the same thing.

Also note that $elemMatch does not apply after processing $unwind, since the content is in fact no longer an array. Simple dot notation applies to the elements that are now in individual documents.