I have a task schema -

const taskSchema = new mongoose.Schema({
  type: { type: String, default: '' },
  completed_at: { type: Date, default: new Date() },
  owner: { type: String, default: '' },
});

Types can be - A, B, and C.

Sample Data -

[{type: A, completed_at: '12-10-2021', owner: 'xyz'},
{type: A, completed_at: '12-10-2021', owner: 'abc'},
{type: C, completed_at: '12-10-2021', owner: 'xyz'},
{type: C, completed_at: '13-10-2021', owner: 'xyz'},
{type: B, completed_at: '13-10-2021', owner: 'xyz'}]

I want something like this -

[{owner: 'xyz', completed_at: [ {completed_at: '12-10-2021', A: 1, B: 0, C: 1}, 
   {completed_at: '13-10-2021', A: 0, B: 1, C: 1} ] },
{owner: 'abc', completed_at: {completed_at: '12-10-2021', A: 1, B: 0, C: 0}}]

I am really confused about how to write a query for this.

CodePudding user response：

Try this one:

db.collection.aggregate([
  {
    $group: {
      _id: {
        owner: "$owner",
        completed_at: "$completed_at"
      },
      type: { $push: "$type" }
    }
  },
  {
    $group: {
      _id: "$_id.owner",
      completed_at: { $push: "$$ROOT" }
    }
  },
  {
    $set: {
      completed_at: {
        $map: {
          input: "$completed_at",
          in: {
            completed_at: "$$this._id.completed_at",
            A: {
              $size: {
                $filter: {
                  input: "$$this.type",
                  as: "t",
                  cond: { $eq: [ "$$t", "A" ] }
                }
              }
            },
            B: {
              $size: {
                $filter: {
                  input: "$$this.type",
                  as: "t",
                  cond: { $eq: [ "$$t", "B" ] }
                }
              }
            },
            C: {
              $size: {
                $filter: {
                  input: "$$this.type",
                  as: "t",
                  cond: { $eq: [ "$$t", "C" ] }
                }
              }
            }
          }
        }
      }
    }
  }
])

Usually I would make A, B and C dynamically, however I am too lazy and like to go to my bed.

Mongo Playground