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! :)

show us how much you had tried, add your aggregation as well — Kannan T
– Kannan T, Commented May 14, 2020 at 11:26
It might be worthwhile to add that even though there's a lot of Mongo magic that can be done to achieve this end result, it is likely the kind of computation that would be far more natural and easier to implement, test and maintain in a backend environment. Consider querying the relevant chunk of data and then analyze it in your backend environment of choice. — Boaz
– Boaz, Commented May 14, 2020 at 14:50
Thanks for this feedback. I am quite new to backend development so feedback on the approach like this is very helpful! — calluna18
– calluna18, Commented May 15, 2020 at 8:18

igorkf · Accepted Answer · 2020-05-14 15:40:35Z

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

Collectives™ on Stack Overflow

Mongo aggregation: Count documents based on array field matching condition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related