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.
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.