Want to get count of items for every date of a given month in MongoDB-CodePudding

I have a task schema as follow :

const taskSchema = new mongoose.Schema({
      uid: { type: String, default: "" },
      status: { type: String, default: "" },
      created_at: {type: Date}
    }

I want to query for all tasks, grouping them in a way such that I get an object with the count of tasks with different statuses for all dates of a given month.

Example - Assuming we are doing this for the month of June,

    task = [
  {
    uid: '1',
    status: 'completed',
    created_at: 29 - 06 - 2021,
  },
  {
    uid: '2',
    status: 'pending',
    created_at: 27 - 06 - 2021,
  },
  {
    uid: '3',
    status: 'missed',
    created_at: 24 - 06 - 2021,
  },
  {
    uid: '4',
    status: 'completed',
    created_at: 13 - 06 - 2021,
  },
  {
    uid: '5',
    status: 'pending',
    created_at: 24 - 06 - 2021,
  },
];

res = [
  {
    '13-06-2021': {
      pending: 0,
      missed: 1,
      completed: 0,
    },
  },
  {
    '29-06-2021': {
      pending: 0,
      missed: 0,
      completed: 1,
    },
  },
  {
    '27-06-2021': {
      pending: 1,
      missed: 0,
      completed: 0,
    },
  },
  {
    '24-06-2021': {
      pending: 1,
      missed: 1,
      completed: 0,
    },
  },
];

I know the group and count aggregate functions but I am unable to figure out how to implement that.

CodePudding user response：

aggregate

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "created_at": "$created_at"
      },
      "pending": {
        $sum: {
          "$cond": [
            {
              "$eq": [
                "$status",
                "pending"
              ]
            },
            1,
            0
          ]
        }
      },
      "missed": {
        $sum: {
          "$cond": [
            {
              "$eq": [
                "$status",
                "missed"
              ]
            },
            1,
            0
          ]
        }
      },
      "completed": {
        $sum: {
          "$cond": [
            {
              "$eq": [
                "$status",
                "completed"
              ]
            },
            1,
            0
          ]
        }
      },
      
    }
  }
])

mongoplayground