Similar to another question I had (Here). But now I'm trying to count unique and total events on daily basis for each event type, based on the following data shape:

{
    username: "jack",
    events: [
     {
       eventType: "party",
       createdAt: "2022-01-23T10:26:11.214Z",
       visitorInfo: {
            visitorId: "87654321-0ebb-4238-8bf7-87654321"
           }
     },
     {
       eventType: "party",
       createdAt: "2022-01-23T10:26:11.214Z",
       visitorInfo: {
            visitorId: "87654321-0ebb-4238-8bf7-87654321"
           }
     },
     {
       eventType: "party",
       createdAt: "2022-01-23T10:26:11.214Z",
       visitorInfo: {
            visitorId: "01234567-0ebb-4238-8bf7-01234567"
           }
     },
    {
       eventType: "meeting",
       createdAt: "2022-01-23T12:26:11.214Z",
       visitorInfo: {
            visitorId: "87654321-0ebb-4238-8bf7-87654321"
           }
     },
     {
       eventType: "meeting",
       createdAt: "2022-01-23T11:26:11.214Z",
       visitorInfo: {
            visitorId: "87654321-0ebb-4238-8bf7-87654321"
           }
     },
     {
       eventType: "meeting",
       createdAt: "2022-01-23T12:26:11.214Z",
       visitorInfo: {
            visitorId: "01234567-0ebb-4238-8bf7-01234567"
           }
     },
     {
       eventType: "party",
       createdAt: "2022-01-30T10:26:11.214Z",
       visitorInfo: {
            visitorId: "12345678-0ebb-4238-8bf7-12345678"
           }
     },
    {
       eventType: "party",
       createdAt: "2022-01-30T10:16:11.214Z",
       visitorInfo: {
            visitorId: "12345678-0ebb-4238-8bf7-12345678"
           }
     },
    {
       eventType: "meeting",
       createdAt: "2022-01-30T12:36:11.224Z",
       visitorInfo: {
            visitorId: "12345678-0ebb-4238-8bf7-12345678"
           }
     },
    {
       eventType: "meeting",
       createdAt: "2022-01-30T11:46:11.314Z",
       visitorInfo: {
            visitorId: "12345678-0ebb-4238-8bf7-12345678"
           }
     }
       ]

    }

I'm trying to count events (all and unique ones based on visitorId) on date (daily). This is what I have so far (thanks to @R2D2's guide on the approach):

 Event.aggregate([
  { $match: { username: 'jack' } },

  { $unwind: "$events" },

  {
    $project: {
      totalPartyEvents: {
        $cond: [
          {
            $eq: ["$events.eventType", "party"],
          },
          1,
          0,
        ],
      },

    uniquePartyEvents: { // where I'm stuck. I need to count unique events based on visitorId on current date for 'party' event type.
        $cond: [
          {
            $eq: ["$events.eventType", "party"],
          },
          1,
          0,
        ],
      },

      totalMeetingEvents: {
        $cond: [
          {
            $eq: ["$events.eventType", "meeting"],
          },
          1,
          0,
        ],
      },

    uniqueMeetingEvents: { // do the same for other events. maybe there's a better way to combine these (with facets).
        $cond: [
          {
            $eq: ["$events.eventType", "meeting"],
          },
          1,
          0,
        ],
      },

      date: "$events.createdAt",
    },
  },

  {
    $group: {
      _id: {
        $dateToString: { format: "%Y-%m-%d", date: "$date" },
      },

      totalPartyEvents: {
        $sum: "$totalMeetingEvents",
      },
      uniquePartyEvents: {
        $sum: "$totalMeetingEvents",
      },
    
      totalMeetingEvents: {
        $sum: "$totalMeetingEvents",
      },

      uniqueMeetingEvents: {
        $sum: "$uniqueMeetingEvents",
      },
    },
  },
  {
    $project: {
      date: "$_id",
      uniquePartyEvents: 1,
      totalPartyEvents: 1,
      totalMeetingEvents:1,
      uniqueMeetingEvents: 1,

    },
  },
  {
    $group: {
      _id: "0",
      dateAndEventFrequency: {
        $push: "$$ROOT",
      },
    },
  },
  {
    $project: {
      _id: 0,
      dateAndEventFrequency: 1,
    },
  },
]);

I tried using $addToSet but it's not used with $project (it works with $group). Any new approach is welcome based on the data shape and the desired result I'm expecting. I used $project because I was already using it.

Basically what I'm hoping to get in the end:

dateAndEventFrequency: [
    {
    _id: "2022-01-23",
    totalPartyEvents: 3,
    uniquePartyEvents: 2,
    totalMeetingEvents: 3,
    uniqueMeetingEvents: 2,
    date: "2022-01-23",
    },
   {
    _id: "2022-01-30",
    totalPartyEvents: 2,
    uniquePartyEvents: 1,
    totalMeetingEvents: 2,
    uniqueMeetingEvents: 1,
    date: "2022-01-30",
    },
]

I'm using Mongoose and Nodejs. Any help or guidance is appreciated. Thanks!

CodePudding user response：

mongo playground

db.collection.aggregate([
  {
    $match: {
      username: "jack"
    }
  },
  {
    "$unwind": "$events"
  },
  {
    "$match": {
      "events.eventType": {
        "$in": [
          "meeting",
          "party"
        ]
      }
    }
  },
  {
    "$group": {
      "_id": {
        date: {
          "$dateToString": {
            format: "%Y-%m-%d",
            date: "$events.createdAt"
          }
        },
        "visitorId": "$events.visitorInfo.visitorId",
        "eventType": "$events.eventType"
      },
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$group": {
      "_id": {
        "date": "$_id.date",
        "eventType": "$_id.eventType"
      },
      "uniqueTotal": {
        "$sum": 1
      },
      total: {
        "$sum": "$count"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.date",
      "partyUniqueTotal": {
        "$sum": {
          "$cond": [
            {
              $eq: [
                "$_id.eventType",
                "party"
              ],
              
            },
            "$uniqueTotal",
            0
          ]
        }
      },
      "totalPartyEvents": {
        "$sum": {
          "$cond": [
            {
              $eq: [
                "$_id.eventType",
                "party"
              ],
              
            },
            "$total",
            0
          ]
        }
      },
      "meetingUniqueTotal": {
        "$sum": {
          "$cond": [
            {
              $eq: [
                "$_id.eventType",
                "meeting"
              ],
              
            },
            "$uniqueTotal",
            0
          ]
        }
      },
      "totalmeetingEvents": {
        "$sum": {
          "$cond": [
            {
              $eq: [
                "$_id.eventType",
                "meeting"
              ],
              
            },
            "$total",
            0
          ]
        }
      }
    }
  }
])