MongoDB聚合查询$gte/$lte日期相等时无结果问题排查

问题现象 #

MongoDB聚合查询时，日期范围跨两天（如startDate=2023-11-26、endDate=2023-11-27）能正常返回结果，但当startDate与endDate为同一天时，返回的campaigns数组为空。已确认当日存在对应数据，问题出在日期比较逻辑上。

现有代码 #

async getCampaigns(query, res) {
console.log(query);

try {
  let { offset = 0, limit = 30, startDate, endDate } = query;

  const startDateString = startDate;
  endDate = new Date(endDate);
  offset = parseInt(offset);
  limit = Math.min(
    parseInt(limit),
    await this.campaignModel.countDocuments(),
  );
  startDate = startDate
    ? new Date(startDate)
    : new Date(endDate.getTime() - 14 * 24 * 60 * 60 * 1000);
  
  if (isNaN(offset) || isNaN(limit)) {
    throw new BadRequestError('Invalid offset or limit');
  }
  const dates = getAllDatesBetween(`${startDateString}/${endDate}`);
  const campaigns = await this.campaignModel.aggregate([
    { $addFields: { datesArray: { $objectToArray: '$dates' } } },
    { $unwind: '$datesArray' },
    {
      $match: {
        'datesArray.k': {
          $gte: startDate.toISOString(),
          $lte: endDate.toISOString(),
        },
      },
    },
    {
      $group: {
        _id: '$_id',
        totalCost: { $sum: '$datesArray.v.cost' },
        document: { $first: '$$ROOT' },
      },
    },
    { $sort: { totalCost: -1 } },
    { $skip: offset },
    { $limit: limit },
    {
      $replaceRoot: {
        newRoot: {
          $mergeObjects: ['$document', { totalCost: '$totalCost' }],
        },
      },
    },
  ]);
  console.log(campaigns);
...
} catch (error) {
  throw new InternalServerError('Internal Server Error');
}}

示例文档 #

[
  {
    "_id": "666",
    "trafficSourceName": "bla",
    "name": "Sorry",
    "keyword": "What Happens At A Cremation Australia",
    "status": "active",
    "limitsFilter": {
        "limitType": "budget_limits",
        "dailyLimit": 7500,
        "overallLimit": 100000000,
        "splitDailyLimitEvenly": 0
    },
    "dates": {
        "2023-11-25": {}, 
        "2023-11-26": {}, 
        "2023-11-27": {}
    }
  },
  {
    "_id": "444",
    "trafficSourceName": "bla",
    "name": "Sorry",
    "keyword": "What Happens At A Cremation United States",
    "status": "active",
    "limitsFilter": {
        "limitType": "budget_limits",
        "dailyLimit": 20000,
        "overallLimit": 100000000,
        "splitDailyLimitEvenly": 0
    },
    "dates": {
        "2023-11-25": {}, 
        "2023-11-26": {}, 
        "2023-11-27": {}
    }
  }
]

问题原因 #

文档中dates的key是**YYYY-MM-DD格式的纯日期字符串**，但代码里将startDate和endDate通过toISOString()转换为带时间戳的字符串（如"2023-11-26T00:00:00.000Z"）。字符串比较时，"2023-11-26"会小于"2023-11-26T00:00:00.000Z"，导致同一天的$gte/$lte条件无法匹配到文档中的日期key，最终返回空数组。

修复方案 #

将查询用的日期格式统一为YYYY-MM-DD字符串，替换toISOString()的用法：

修改后的完整代码 #

async getCampaigns(query, res) {
console.log(query);

try {
  let { offset = 0, limit = 30, startDate, endDate } = query;

  const startDateString = startDate;
  endDate = new Date(endDate);
  offset = parseInt(offset);
  limit = Math.min(
    parseInt(limit),
    await this.campaignModel.countDocuments(),
  );
  startDate = startDate
    ? new Date(startDate)
    : new Date(endDate.getTime() - 14 * 24 * 60 * 60 * 1000);
  
  // 新增：将Date对象转为YYYY-MM-DD格式字符串
  const formatDateToYYYYMMDD = (date) => {
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, '0');
    const day = String(date.getDate()).padStart(2, '0');
    return `${year}-${month}-${day}`;
  };
  
  if (isNaN(offset) || isNaN(limit)) {
    throw new BadRequestError('Invalid offset or limit');
  }
  const dates = getAllDatesBetween(`${startDateString}/${endDate}`);
  const campaigns = await this.campaignModel.aggregate([
    { $addFields: { datesArray: { $objectToArray: '$dates' } } },
    { $unwind: '$datesArray' },
    {
      $match: {
        'datesArray.k': {
          $gte: formatDateToYYYYMMDD(startDate),
          $lte: formatDateToYYYYMMDD(endDate),
        },
      },
    },
    {
      $group: {
        _id: '$_id',
        totalCost: { $sum: '$datesArray.v.cost' },
        document: { $first: '$$ROOT' },
      },
    },
    { $sort: { totalCost: -1 } },
    { $skip: offset },
    { $limit: limit },
    {
      $replaceRoot: {
        newRoot: {
          $mergeObjects: ['$document', { totalCost: '$totalCost' }],
        },
      },
    },
  ]);
  console.log(campaigns);
...
} catch (error) {
  throw new InternalServerError('Internal Server Error');
}}

验证说明 #

修改后，当startDate与endDate为同一天时，查询条件会精准匹配文档中对应的YYYY-MM-DD格式key，即可正常返回当日数据。

内容的提问来源于stack exchange，提问作者xoreks