Mongo db Aggregate - $sum to add values if between dates-CodePudding

I'm looking to write a query to aggregate the quantity based on dates.

I have documents that look like this:

{
    "_id" : 1234,
    "itemNumber" : "item 1",
    "date" : ISODate("2021-10-26T21:00:00Z"),
    "quantity" : 1,
    "__v" : 0
}

And a query like this:

//monogoose 

myCollection.aggregate().group({
        _id: '$itemNumber',
        ninetyDays: {
            $sum: {
                $and: {
                    $gte: ["date", dayjs().subtract(90, 'd').toDate()],
                    $lt: ["date", dayjs().toDate()]
                }

            }
        }
    })

In the query above ninetyDays is always 0.

I'm basically looking to get the sum of the quantity given a date range.

Help is much appreciated.

Thank you

CodePudding user response：

You can use $cond to sum 1 or 0 if your condition is match.

Assuming your date expression is correct this should works, but don't forger $ in date.

db.collection.aggregate([
  {
    "$group": {
      "_id": "$itemNumber",
      "ninetyDays": {
        "$sum": {
          "$cond": {
            "if": {
              "$and": [
                $gte: ["$date", dayjs().subtract(90, 'd').toDate()],
                $lt: ["$date", dayjs().toDate()]
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      }
    }
  }
])

Example here where I've used mongo $$NOW but if your date works is easier to use your code.