Hi, I have 2 collection, Buyer and User. I would like to plot a graph that at each week what was the number of buyers signed up per each company. I need to merge in User to the buyers collection to filter out test users and non-verified buyers. I have the following aggregation query that returns the data that need to be plotted but cannot figure it out how to actually plot it because of 2 reasons:
- I need to implement cumulativeCount by myself instead of using the one provided by the charts because I am groupping with 2 fields and not one (and in that case the Periodic toggle disappears from the UI)
- I cannot use $lookup in a query
This is the aggregation query on Buyer collection that returns the data I would like to plot:
[
{
$lookup:
{
from: "User",
localField: "userId",
foreignField: "_id",
as: "user",
},
},
{
$unwind:
{
path: "$user",
},
},
{
$match:
{
"user.emailVerified": {
$ne: null,
},
"user.email": {
$not: {
$regex: "test",
},
},
},
},
{
$group:
{
_id: {
week: {
$dateTrunc: {
date: "$createdAt",
unit: "week"
},
},
company: "$company"
},
count: {
$count: {},
},
},
},
{
$setWindowFields: {
partitionBy: "$_id.company",
sortBy: { "_id.week": 1 },
output: {
cumulativeTotal: {
$sum: "$count",
window: { documents: ["unbounded", "current"] }
}
}
}
}
]