Mongo aggregation: Count documents based on array field matching condition

Question

I am trying to collects some statistics from a mongo collection using an aggregation pipeline but can not seem to solve my issue.

I have a collection of documents looking like this.

{
    _id: “36723678126”,
    dates: [ 2020-03-10T10:17:48.799Z, 2020-03-10T08:46:50.347Z, 2019-07-11T13:16:17.294Z ]
}

I would like count the number of documents that meet the following conditions

• At least one date in the last 30 days
• No dates between 60 and 30 days ago
• At least one date before 60 days ago

Would really appreciate any help! :)

Answer 1

This should solve the problem (https://mongoplayground.net/p/B3LbEo8UaHA):

Test data:

[
  {
    _id: 1,
    dates: [
      ISODate("2020-03-10T10:17:48.799Z"),
      ISODate("2020-03-10T08:46:50.347Z"),
      ISODate("2019-07-11T13:16:17.294Z")
    ]
  },
  {
    _id: 2,
    dates: [
      ISODate("2020-04-10T10:17:48.799Z"),
      ISODate("2020-05-10T08:46:50.347Z"),
      ISODate("2019-10-11T13:16:17.294Z")
    ]
  }
]

Query:

db.collection.aggregate([
  // create the new fields based on the date rules and count occurences of array
  {
    $addFields: {
      "last_30_days": {
        $sum: {
          $map: {
            "input": "$dates",
            "as": "d",
            "in": {
              $cond: {
                "if": {
                  $lte: [
                    "$$d",
                    {
                      $subtract: [
                        "$$NOW",
                        2592000000 // 30 days
                      ]
                    }
                  ]
                },
                "then": 1,
                "else": 0
              }
            }
          }
        }
      },
      "between_30_60": {
        $sum: {
          $map: {
            "input": "$dates",
            "as": "d",
            "in": {
              $cond: {
                "if": {
                  $and: [
                    {
                      $lt: [
                        "$$d",
                        {
                          $subtract: [
                            "$$NOW",
                            2592000000 // days
                          ]
                        }
                      ]
                    },
                    {
                      $gt: [
                        "$$d",
                        {
                          $subtract: [
                            "$$NOW",
                            5184000000 // 60 days
                          ]
                        }
                      ]
                    }
                  ]
                },
                "then": 1,
                "else": 0
              }
            }
          }
        }
      },
      "last_60_days": {
        $sum: {
          $map: {
            "input": "$dates",
            "as": "d",
            "in": {
              $cond: {
                "if": {
                  $lte: [
                    "$$d",
                    {
                      $subtract: [
                        "$$NOW",
                        5184000000
                      ]
                    }
                  ]
                },
                "then": 1,
                "else": 0
              }
            }
          }
        }
      }
    }
  },
  // find which document meet the conditions (1 for true, 0 for false)
  {
    $project: {
      "meet_conditions": {
        $cond: {
          "if": {
            $and: [
              {
                $gt: [
                  "$last_30_days",  // at least one occurence
                  0
                ]
              },
              {
                $eq: [
                  "$between_30_60",  // no occurences
                  0
                ]
              },
              {
                $gt: [
                  "last_60_days",  // at least one occurence
                  0
                ]
              }
            ]
          },
          "then": 1,
          "else": 0
        }
      }
    }
  },
  // count documents, summing the meet_conditions field (because they are 0 or 1)
  {
    $group: {
      _id: null,
      count: {
        $sum: "$meet_conditions"
      }
    }
  }
])

Result:

[
  {
    "_id": null,
    "count": 1
  }
]

So in the example above, there's just one documents that meet your conditions.

Obs.: To calculate the days you have to do (the bold numbers are the day you want):

1 * 1000 * 60 * 60 * 24 * 30 = 2592000000ms = 30 days

1 * 1000 * 60 * 60 * 24 * 60 = 5184000000ms = 60 days