mongo query to correctly use the densify feature #15943
Replies: 4 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 |
|
@vkarpov15 thanks for looking into this. I want the densify bounds to match the |
|
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. |
|
@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 |
|
|
|
The extra records come from how $densify and $match interact with timezone offsets in your date range. Your $match uses ISODate("2025-12-30T05:30:00.000Z") as the lower bound, which is midnight in IST (UTC+5:30). The $densify step fills in every calendar date within the full range including the boundary dates. Depending on whether the boundaries are inclusive or exclusive, and whether $densify treats the bounds as UTC or as a time-of-day anchor, you can end up with one or two extra dates at the edges. A few things to check. First, verify that your date range is exactly 7 days. The range from 2025-12-30T05:30:00Z to 2026-01-06T05:30:00Z spans exactly 7 days in IST but includes 8 UTC calendar dates (Dec 30 through Jan 6). The $densify step works in UTC, so it generates one record per UTC date within the full range, which gives 8 slots before the $match has any effect. After grouping by day, most of those slots end up with zero counts from the fill step but the total can be 8 or 9 depending on how your range falls. The fix is to align your bounds to UTC midnight rather than IST midnight. Set the lower bound to 2025-12-30T00:00:00.000Z and the upper bound to 2026-01-05T23:59:59.999Z to cover exactly 7 UTC days. Second, make sure the bounds in your $densify stage exactly match your $match range. If the densify range is slightly wider than the match range, records outside the match are generated by densify but then survive because the $group absorbs them into the nearest matching bucket. Third, the $densify bounds field supports full and range but not a custom date list, so the cleanest approach is to compute your bounds on the application side in UTC before passing them to the aggregation. |
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.All reactions