mongo query to correctly use the densify feature #15943
Replies: 3 comments 2 replies
-
|
That's expected because your date range for densify is Dec 29 - Jan 7 - that's 9 days. 1 | 2025-12-29 18:30 |
Beta Was this translation helpful? Give feedback.
-
|
@vkarpov15 thanks for looking into this. I want the densify bounds to match the |
Beta Was this translation helpful? Give feedback.
-
|
Then write this:
Also, if you're struggling to get the aggregation framework to do what you need, remember that you don't necessarily need to use the aggregation framework. |
Beta Was this translation helpful? Give feedback.
-
|
@vkarpov15 thanks for looking into this issue. I have modified the query and passing the same date values to the densify bounds and match stage. But the in the result the label value isn't matching with the month in the import { addDays, startOfDay, subDays } from "date-fns";
import { toZonedTime } from "date-fns-tz";
import mongoose from "mongoose";
import { WorkoutAttempt } from "../../../models/WorkoutAttempt";
export async function testWorkouts() {
const tz = "Asia/Kolkata";
const noOfDays = 365
// Get current time in the target timezone
const zonedNow = toZonedTime(new Date(), tz);
// endLocal = start of current day in that timezone
const endLocal = startOfDay(zonedNow);
// startLocal = endLocal - (noOfDays - 1) days
const startLocal = subDays(endLocal, noOfDays - 1);
// endLocalPlus1 = endLocal + 1 day to include the results of current day as well.
const endLocalPlus1 = addDays(endLocal, 1);
const data = await WorkoutAttempt.aggregate([
{
$match: {
$expr: {
$and: [
{ $gte: ["$createdAt", startLocal] },
{ $lt: ["$createdAt", endLocalPlus1] },
{ $eq: ["$userId", new mongoose.Types.ObjectId("xxxxxxxxx")] }
]
}
}
},
{
$lookup: {
from: "users",
localField: "userId",
foreignField: "_id",
as: "user"
}
},
{ $unwind: "$user" },
{ $addFields: { noOfDays: noOfDays } },
{ $match: { type: "QUICKTRAIN" } },
{
$addFields: {
localDay: {
$dateTrunc: {
date: "$createdAt",
unit: "day",
timezone: "Asia/Kolkata"
}
}
}
},
{
$group: {
_id: {
$dateTrunc: {
date: "$createdAt",
unit: "day",
timezone: "Asia/Kolkata"
}
},
totalSessions: { $sum: 1 },
noOfDays: { $first: "$noOfDays" }
}
},
{ $sort: { _id: 1 } },
{
$densify: {
field: "_id",
range: {
step: 1,
unit: "day",
bounds: [startLocal, endLocal]
}
}
},
{
$fill: {
output: {
totalSessions: { value: 0 }
}
}
},
{ $addFields: { noOfDays: { $literal: noOfDays } } },
{
$project: {
_id: 1,
totalSessions: 1,
periodKey: {
$switch: {
branches: [
{ case: { $lte: ["$noOfDays", 7] }, then: "$_id" },
{ case: { $and: [{ $gt: ["$noOfDays", 7] }, { $lte: ["$noOfDays", 90] }] }, then: "$_id" },
{
case: { $and: [{ $gt: ["$noOfDays", 90] }, { $lte: ["$noOfDays", 365] }] },
then: {
$dateTrunc: {
date: "$_id",
unit: "month",
timezone: "Asia/Kolkata"
}
}
}
],
default: { $dateTrunc: { date: "$_id", unit: "year" } }
}
},
label: {
$switch: {
branches: [
{
case: { $lte: ["$noOfDays", 7] },
then: {
$arrayElemAt: [
["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"],
{ $subtract: [{ $isoDayOfWeek: "$_id" }, 1] }
]
}
},
{
case: { $and: [{ $gt: ["$noOfDays", 7] }, { $lte: ["$noOfDays", 90] }] },
then: {
$dateToString: {
date: "$_id",
format: "%Y-%m-%d",
timezone: "Asia/Kolkata"
}
}
},
{
case: { $and: [{ $gt: ["$noOfDays", 90] }, { $lte: ["$noOfDays", 365] }] },
then: {
$arrayElemAt: [
["Jan", "Feb", "Mar", "Apr", "May", "Jun",
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec"],
{ $subtract: [{ $month: "$_id" }, 1] }
]
}
}
],
default: {
$dateToString: {
date: "$_id",
format: "%Y",
timezone: "Asia/Kolkata"
}
}
}
}
}
},
{
$group: {
_id: "$periodKey",
label: { $first: "$label" },
totalSessions: { $sum: "$totalSessions" }
}
},
{ $sort: { _id: 1 } },
{ $project: { _id: 1, label: 1, totalSessions: 1 } }
])
return data
}Result of the above query |
Beta Was this translation helpful? Give feedback.
-
|
|
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
-
Hi Team,
I'm building a query for a dashboard chart to display some workouts done per day/month etc,. I'm using the below query to densify the missing dates and add zero value if the date is missing and grouping by date. But the problem is if the
noOfDaysis value is 7 I'm getting 9 records. I'm expecting 7 records (one for each day). Below is the query. Also I want the densify bounds to be consistent with the $gte and $lte of createdAt filters as these are dynamically coming from the front end. Any help in this regard would be highly appreciated.Beta Was this translation helpful? Give feedback.
All reactions