get customer active days from mongodb document-CodePudding

I have a mongodb document for customer streaming activations.

[
  {
    "_id": 1,
    "email": "[email protected]",
    "packageid": "movies",
    "command": "activated",
    "tid": "123",
    "createdAt": ISODate("2021-06-08")
  },
  {
    "_id": 2,
    "email": "[email protected]",
    "packageid": "movies",
    "command": "activated",
    "tid": "124",
    "createdAt": ISODate("2021-06-20")
  },
  {
    "_id": 3,
    "email": "[email protected]",
    "packageid": "movies",
    "command": "deactivated",
    "tid": "1234",
    "createdAt": ISODate("2021-06-10")
  },
  {
    "_id": 4,
    "email": "[email protected]",
    "packageid": "movies",
    "command": "deactivated",
    "tid": "1244",
    "createdAt": ISODate("2021-06-22")
  },
  {
    "_id": 5,
    "email": "[email protected]",
    "packageid": "movies",
    "command": "activated",
    "tid": "123",
    "createdAt": ISODate("2021-06-11")
  },
  {
    "_id": 6,
    "email": "[email protected]",
    "packageid": "movies",
    "command": "activated",
    "tid": "1244",
    "createdAt": ISODate("2021-06-23")
  },
  {
    "_id": 7,
    "email": "[email protected]",
    "packageid": "movies",
    "command": "deactivated",
    "tid": "1237",
    "createdAt": ISODate("2021-06-15")
  },
  {
    "_id": 8,
    "email": "[email protected]",
    "packageid": "movies",
    "command": "deactivated",
    "tid": "1244",
    "createdAt": ISODate("2021-06-25")
  },
  
]

now I wanna group by email and get each customer activated days for specific time frame. let's say 1 month. I've been trying this for few hours

{
"email":"[email protected]"
"packageid":"movies",
"days": 3
},
{
"email":"[email protected]"
"packageid":"movies",
"days": 5
},
{
"email":"[email protected]"
"packageid":"movies",
"days": 3
},
{
"email":"[email protected]"
"packageid":"movies",
"days": 3
}

edit: any user can activate then deactivate the service any time they want sometimes users do activate and deactivate multiple times within a same month. I want to find out how many days customer was activated.

CodePudding user response：

We can use $setWindowFields to compute the $dateDiff and $group the sum.

db.collection.aggregate([
  {
    "$setWindowFields": {
      "partitionBy": {
        email: "$email",
        packageid: "$packageid"
      },
      "sortBy": {
        "createdAt": 1
      },
      "output": {
        "next": {
          $shift: {
            output: "$createdAt",
            by: 1
          }
        }
      }
    }
  },
  {
    $match: {
      "command": "activated"
    }
  },
  {
    $project: {
      email: "$email",
      packageid: "$packageid",
      "days": {
        "$dateDiff": {
          startDate: "$createdAt",
          endDate: {
            $ifNull: [
              "$next",
              "$$NOW"
            ]
          },
          unit: "day"
        }
      }
    }
  }
])

Here is the Mongo Playground for your reference.