Mongoose聚合查询无法获取期望数据的问题求助

以下是针对你的查询结果为空、无法获取user_serial_id的排查点及解决方案：

1. 日期格式解析错误导致$match过滤所有数据 #

你构造日期字符串的方式可能存在格式不兼容问题：

let startDateTimeStr = start_date + "T00:00:00.0000000";
let endDateTimeStr = end_date + "T00:00:00.0000000";

• 若start_date/end_date不是标准ISO日期格式（如"2024-05-20"），拼接后的字符串无法被MongoDB正确解析为日期类型，导致$match阶段没有匹配到任何文档。
• 解决：直接使用Date对象而非字符串拼接，确保MongoDB能正确识别：

  let startDateTime = new Date(start_date);
  startDateTime.setHours(0, 0, 0, 0);
  let endDateTime = new Date(end_date);
  endDateTime.setHours(0, 0, 0, 0);
  
  matchCase.push({
    "start.dateTime": { $gte: startDateTime },
    "end.dateTime": { $lte: endDateTime },
  });
  

2. $lookup阶段邮箱匹配不精确 #

$lookup依赖nonDeclinedEmails数组与users集合的email字段匹配，需确保：

• appointments集合中attendees.email的格式（大小写、空格、特殊字符）与users.email完全一致，MongoDB的字符串匹配是大小写敏感的。
• users集合中确实存在对应邮箱的文档，可单独查询验证：

  // 验证是否存在匹配的用户
  const testUser = await User.findOne({ email: "目标邮箱" });
  console.log(testUser);
  

3. $project阶段嵌套数组提取错误 #

resource: "$nonDeclinedUsers.user_serial_id"会生成二维数组（因为nonDeclinedUsers是数组，每个元素包含user_serial_id），而非预期的一维数组。若你需要扁平化的ID列表，需用$reduce或$concatArrays处理：

let projectQuery = {
  $project: {
    user_id: 1,
    description: 1,
    title: "$subject",
    start: "$start.dateTime",
    end: "$end.dateTime",
    type: "appointment",
    resource: {
      $reduce: {
        input: "$nonDeclinedUsers",
        initialValue: [],
        in: { $concatArrays: ["$$value", ["$$this.user_serial_id"]] }
      }
    },
    recurrence: "$recurrence",
    editable: { $toBool: 0 }
  }
};

修正后的完整聚合查询示例 #

// 修正日期处理
let startDateTime = new Date(start_date);
startDateTime.setHours(0, 0, 0, 0);
let endDateTime = new Date(end_date);
endDateTime.setHours(0, 0, 0, 0);

matchCase.push({
  "start.dateTime": { $gte: startDateTime },
  "end.dateTime": { $lte: endDateTime },
});

matchCase.push({
  "attendees.status.response": { $ne: "declined" },
});

let addFieldsQuery = {
  $addFields: {
    nonDeclinedEmails: {
      $map: {
        input: {
          $filter: {
            input: "$attendees",
            as: "attendee",
            cond: { $ne: ["$$attendee.status.response", "declined"] }
          }
        },
        as: "nonDeclined",
        in: "$$nonDeclined.email"
      }
    }
  }
};

let lookupQuery = {
  $lookup: {
    from: "users",
    localField: "nonDeclinedEmails",
    foreignField: "email",
    as: "nonDeclinedUsers"
  }
};

let projectQuery = {
  $project: {
    user_id: 1,
    description: 1,
    title: "$subject",
    start: "$start.dateTime",
    end: "$end.dateTime",
    type: "appointment",
    resource: {
      $reduce: {
        input: "$nonDeclinedUsers",
        initialValue: [],
        in: { $concatArrays: ["$$value", ["$$this.user_serial_id"]] }
      }
    },
    recurrence: "$recurrence",
    editable: { $toBool: 0 }
  }
};

let aggregatQuery = [];

if (matchCase.length > 0) {
  aggregatQuery.push({ $match: { $and: matchCase } });
}

aggregatQuery.push(addFieldsQuery);
aggregatQuery.push(lookupQuery);

if (Object.keys(projectQuery).length > 0) {
  aggregatQuery.push(projectQuery);
}

return aggregatQuery;

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