Mongoose/Mongo按创建日期统计引用数组内对象数量

我在MongoDB的user集合中定义了两个关联工单的数组字段：

assignedTickets: [ { type: mongoose.Schema.ObjectId, ref: 'Ticket', index: true, }, ],
resolvedTickets: [ { type: mongoose.Schema.ObjectId, ref: 'Ticket', index: true, }, ]

我需要基于关联Ticket对象的createdAt日期，统计这两个字段中对应日期的工单数量（比如统计2018-05-03当天创建的工单数量）。目前我已经写了部分代码：

const start = new Date(); start.setHours(0, 0, 0, 0); const end...

要实现这个需求，我们可以利用MongoDB的聚合管道来关联Ticket集合并进行统计，以下是完整的实现方案：

针对单个用户的统计 #

如果你需要统计某个特定用户在目标日期的assigned和resolved工单数量，可以使用下面的聚合查询：

// 定义目标日期的时间范围（当天00:00到23:59:59.999）
const targetDate = new Date('2018-05-03');
const start = new Date(targetDate);
start.setHours(0, 0, 0, 0);
const end = new Date(targetDate);
end.setHours(23, 59, 59, 999);

const result = await User.aggregate([
  // 可选：如果只针对特定用户，添加$match条件筛选
  // { $match: { _id: mongoose.Types.ObjectId('目标用户ID') } },
  
  // 关联Ticket集合，获取assignedTickets的完整工单信息
  {
    $lookup: {
      from: 'tickets', // Ticket集合的名称
      localField: 'assignedTickets',
      foreignField: '_id',
      as: 'assignedTicketsDetails'
    }
  },
  // 关联Ticket集合，获取resolvedTickets的完整工单信息
  {
    $lookup: {
      from: 'tickets',
      localField: 'resolvedTickets',
      foreignField: '_id',
      as: 'resolvedTicketsDetails'
    }
  },
  // 计算当天创建的assigned和resolved工单数量
  {
    $addFields: {
      assignedCount: {
        $size: {
          $filter: {
            input: '$assignedTicketsDetails',
            cond: {
              $and: [
                { $gte: ['$$this.createdAt', start] },
                { $lte: ['$$this.createdAt', end] }
              ]
            }
          }
        }
      },
      resolvedCount: {
        $size: {
          $filter: {
            input: '$resolvedTicketsDetails',
            cond: {
              $and: [
                { $gte: ['$$this.createdAt', start] },
                { $lte: ['$$this.createdAt', end] }
              ]
            }
          }
        }
      }
    }
  },
  // 只保留需要的结果字段
  {
    $project: {
      _id: 1, // 保留用户ID（可选）
      assignedCount: 1,
      resolvedCount: 1
    }
  }
]);

性能优化建议 #

如果你的工单数据量较大，记得给Ticket集合的createdAt字段添加索引，这样能大幅提升日期筛选的效率：

// 在Ticket的Schema中添加索引
TicketSchema.index({ createdAt: 1 });

统计所有用户的总数量（可选） #

如果需要统计所有用户在目标日期的assigned和resolved工单总数，可以调整聚合管道，先展开数组再分组统计：

const totalResult = await User.aggregate([
  { $unwind: '$assignedTickets' },
  {
    $lookup: {
      from: 'tickets',
      localField: 'assignedTickets',
      foreignField: '_id',
      as: 'ticket'
    }
  },
  { $unwind: '$ticket' },
  {
    $match: {
      'ticket.createdAt': { $gte: start, $lte: end }
    }
  },
  {
    $group: {
      _id: null,
      totalAssigned: { $sum: 1 }
    }
  }
]);
// 同理可以处理resolvedTickets的统计

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