Counting the total per type per day

Working with Data
,
2

Hello @Awakening_Quasar,

Well, It is possible to achieve your required result in an aggregation query, but I would not recommend doing this kind of grouping operation in an aggregation query when your collection has a large number of documents. Try to manipulate your result on your client/server-side application so you can avoid unnecessary load in the database server.

If you still want to do this operation in an aggregation query or just for the knowledge follow the below aggregation query.

  • first $group to group by date and role, convert the date to your required format and get the total count
  • The second $group is to group by just date and get array roles in the array in your desired format, we have prepared a key-value format so we can convert it to an object.
  • $replaceRoot to prepare the final your desired object
  {
    $group: {
      _id: {
        date: {
          $dateToString: {
            date: "$createdAt",
            format: "%Y-%m-%d",
            // timezone: "Put your timezone if you want to convert"
          }
        },
        role: "$role"
      },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: "$_id.date",
      roles: {
        $push: {
          k: {
            $concat: [
              "role_",
              { $toString: "$_id.role" }
            ]
          },
          v: "$count"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          { date: "$_id" },
          { $arrayToObject: "$roles" }
        ]
      }
    }
  }

You can use the $addFields stage instead of the $replaceRoot stage,

  {
    $addFields: {
      roles: {
        $arrayToObject: "$roles"
      }
    }
  }

There are multiple ways to achieve this result, but make sure to create an index on the createdAt and role properties to avoid the collection scan if you have a huge number of data.