I have a model that looks like this:
{
tokens: [
{
value: {
type: String,
required: true,
unique: true,
},
origin: {
type: String,
default: 'Unknown',
},
grabbedAt: {
type: Date,
default: Date.now,
},
},
], ...
}
Now I want to format the data in the following way that all "tokens" with a date of the past 12 days are returned and grouped by their origin with a count per day.
So the result would look like this: [{ origin: 'Unknown', data: [0,1,2,...] }, { origin: 'origin2', data: [1,10,...] }]
The data array would hold the count of tokens acquired on past 12 days, beginning with the first day to the 12th day.
I already tried something like this:
Account.aggregate([
{ $unwind: '$tokens' },
{ $match: { 'tokens.grabbedAt': { $gte: beforeDate } } },
{
$group: {
_id: { origin: '$tokens.origin', date: '$tokens.grabbedAt' },
count: { $sum: 1 },
},
},
{ $project: { _id: 0, origin: '$_id.origin', date: '$_id.date', count: '$count' } },
{ $sort: { date: 1 } },
]);
But using this code each date and origin is included multiple times. So how can I "join" or merge these two $groups?
One way to do it, is $unwind and $group the tokens by origin and then use 3 steps to create a list of the 12 needed dates. Then we can use a $set step to create the empty dates objects that were missing. Now we can $concatArrays our real measurements with the "artificial" ones of the empty days. The last part is just to group and sum it up.
db.collection.aggregate([
{$unwind: "$tokens"},
{$match: {"tokens.grabbedAt": {$gte: beforeDate}}},
{$sort: {"tokens.date": 1}},
{
$group: {
_id: "$tokens.origin",
res: {
$push: {
dateString: {$dateToString: {date: "$tokens.grabbedAt",
format: "%Y-%m-%d"}}, count: 1
}
}
}
},
{
$addFields: {startDate: beforeDate, range: {$range: [0, 12, 1]}}
},
{
$set: {
dateStrings: {
$map: {
input: "$range",
in: {
dateString: {
$dateToString: {
date: {
$add: [
"$startDate",
{$multiply: ["$$this", 24, 60, 60, 1000]}
]
},
format: "%Y-%m-%d"
}
},
count: 0
}
}
}
}
},
{$project: {data: {$concatArrays: ["$dateStrings", "$res"]}}},
{$unwind: "$data"},
{$group: {
_id: {origin: "$_id", date: "$data.dateString"},
count: {$sum: "$data.count"}
}
},
{$group: {_id: "$_id.origin", date: {$push: "$count"}}}
])