如何统计Mongoose查询结果中用户借阅次数并生成统计数组

问题背景 #

现有MongoDB模型及查询数据如下，需要将用户借阅记录按name和course分组，统计每组的借阅次数，转换成指定格式。

模型文件

models/return.js

const mongoose = require('mongoose')
const returnSchema = new mongoose.Schema({
    userId: {
        type: mongoose.Schema.ObjectId,
        ref: 'User'
    },
    bookId: [{
        type: mongoose.Schema.ObjectId,
        ref: 'Book'
    }],
    course: {
        type: String,
    },
    returnedDate: {
        type: Date,
    }
})
module.exports = mongoose.model('Return', returnSchema);

models/user.js

const mongoose = require('mongoose');
const userSchema = new mongoose.Schema({
    name: {
        type: String,
        required: [true, 'Please enter your name'],
    },
    course: {
        type: String,
    },
    section: {
        type: String,
    },
    email: {
        type: String,
        required: [true, 'Please enter your email'],
        unique: true,
        validate: [validator.isEmail, 'Please enter valid email address']
    },
    password: {
        type: String,
        required: [true, 'Please enter your password'],
        select: false
    },
    resetPasswordToken: String,
    resetPasswordExpire: Date
})
module.exports = mongoose.model('User', userSchema);

现有数据

提取name和course后的数组：

[
  {name: 'user1', course: 'BSIT'},
  {name: 'user1', course: 'BSIT'},
  {name: 'user1', course: 'BSIT'},
  {name: 'user1', course: 'BSIT'},
  {name: 'user3', course: 'BTIT'},
  {name: 'user3', course: 'BTIT'},
  {name: 'user2', course: 'BSIT'}
]

目标格式

[
  {name: 'user1', course: 'BSIT', count:'3'},
  {name: 'user2', course: 'BSIT', count:'2'},
  {name: 'user3', course: 'BTIT', count:'2'}
]

解决方案 #

方案一：MongoDB聚合查询（推荐）

直接在数据库层完成关联、分组和统计，避免大量数据传输，性能更优。

const borrowerRanking = await Return.aggregate([
  // 关联User集合，获取用户的name和course
  {
    $lookup: {
      from: 'users', // User模型对应的集合名，注意是复数小写形式
      localField: 'userId',
      foreignField: '_id',
      as: 'userInfo'
    }
  },
  // 展开userInfo数组（$lookup返回的是数组，需展开为单个对象）
  { $unwind: '$userInfo' },
  // 按name和course分组，统计借阅次数
  {
    $group: {
      _id: {
        name: '$userInfo.name',
        course: '$userInfo.course'
      },
      count: { $sum: 1 }
    }
  },
  // 调整字段格式，转换成目标结构
  {
    $project: {
      _id: 0,
      name: '$_id.name',
      course: '$_id.course',
      count: { $toString: '$count' } // 若需数字类型可去掉$toString
    }
  }
])

方案二：JavaScript层面处理已有数组

如果已经获取了提取后的borrowerArr，可以用reduce方法分组计数：

const result = Object.values(borrowerArr.reduce((acc, item) => {
  // 用name+course的组合作为唯一标识键
  const key = `${item.name}-${item.course}`;
  if (!acc[key]) {
    // 首次出现该用户，初始化计数
    acc[key] = { ...item, count: '1' };
  } else {
    // 累计计数
    acc[key].count = `${parseInt(acc[key].count) + 1}`;
  }
  return acc;
}, {}));

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