MongoDB聚合查询如何关联客户集合获取客户完整信息

当然可以实现，你需要在聚合管道中添加$lookup阶段来关联clients集合，从而获取客户名称等详细信息。以下是调整后的聚合查询语句：

db.sales.aggregate([
  {
    $group: {
      _id: {
        month: { $month: "$createdAt" },
        year: { $year: "$createdAt" },
        dayOfWeek: { $dayOfWeek: "$createdAt" },
        stringDay: { $dateToString: { format: "%Y-%m-%d", date: "$createdAt"} },
        week: { $isoWeek: "$createdAt" }
      },
      paymentType: { $first: '$paymentType' },
      clientId: { $first: '$clientId' }, // 保留clientId用于后续关联
      total: { $sum: '$total'},
      count: { $sum: 1 },
      totalAverage: { $avg: '$total'},
    }
  },
  // 添加$lookup关联clients集合
  {
    $lookup: {
      from: "clients", // 关联的目标集合名
      localField: "clientId", // 当前集合中用于关联的字段
      foreignField: "_id", // clients集合中匹配的字段
      as: "clientInfo" // 存储关联结果的数组字段名
    }
  },
  // 展开客户信息数组（一个clientId对应唯一客户）
  {
    $unwind: "$clientInfo"
  },
  {
    $project: {
      total:  { $round: [ "$total", 2 ] },
      year: "$_id.year",
      date: "$_id.date",
      week: "$_id.week",
      numVentas: "$count",
      month: "$_id.month",
      dayOfWeek: "$_id.dayOfWeek",
      stringDay:"$_id.stringDay",
      count: "$count",
      paymentType: "$paymentType",
      // 重构client字段，提取需要的客户信息
      client: {
        _id: "$clientInfo._id",
        name: "$clientInfo.name", // 假设clients集合中有name字段，按需修改
        phone: "$clientInfo.phone" // 可根据实际需求添加其他客户字段
      },
      totalAverage: { $round: [ "$totalAverage", 2 ] },
      stringMonth: {
        $arrayElemAt: [
          [ "", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" ],
          "$_id.month"
        ]
      },
      stringWeek: {
        $switch: {
            branches:[
                { case: { $eq: ["$_id.dayOfWeek", 1] }, then: "Lunes" },
                { case: { $eq: ["$_id.dayOfWeek", 2] }, then: "Martes" },
                { case: { $eq: ["$_id.dayOfWeek", 3] }, then: "Miércoles" },
                { case: { $eq: ["$_id.dayOfWeek", 4] }, then: "Jueves" },
                { case: { $eq: ["$_id.dayOfWeek", 5] }, then: "Viernes" },
                { case: { $eq: ["$_id.dayOfWeek", 6] }, then: "Sábado" },
                { case: { $eq: ["$_id.dayOfWeek", 7] }, then: "Domingo" }
            ],
            default: "Día desconocido"
            }
        }
    }
  },
  {
    $group: {
        _id:  { month: "$month", stringMonth: "$stringMonth", year: "$year"},
        count: { $sum: "$count" },
        total: { $sum: "$total" },
        totalAverage: { $sum: "$totalAverage" },
        sales: {
            $push: { 
                client: "$client", // 这里已经是包含名称的客户对象
                paymentType: "$paymentType",
                numberDay: "$dayOfWeek",
                week: "$week",
                stringWeek: "$stringWeek",
                date: "$stringDay",
                total: "$total",
                count: "$count",
                totalAverage: { $round: [ "$totalAverage", 2 ] }  
            }
        }
    }
  },
  {
    $group: {
        _id: "$_id.year",
        monthsWithSales: { $sum: 1 },
        count: { $sum: "$count" },
        total: { $sum: "$total" },
        totalAverage: { $sum: "$totalAverage" },
        sales: {
            $push: {
                month: "$_id.month",
                stringMonth: "$_id.stringMonth",
                count: "$count",
                total: "$total",
                totalAverage: "$totalAverage",
                sales:"$sales"
            }
        }
    }
  }
])

关键调整说明： #

1. 保留clientId字段：第一次$group阶段将原client字段改为clientId，用于后续关联客户集合。
2. 添加$lookup关联：通过字段匹配将clients集合的对应数据关联到当前聚合文档中。
3. 展开客户信息数组：用$unwind将$lookup返回的数组转为单个对象，方便提取字段。
4. 重构client字段：在$project阶段从关联的clientInfo中提取需要的客户字段，替换原仅含_id的client字段。
5. 后续分组复用新字段：确保最终返回的层级结构中，客户信息包含名称等详细数据。

注意事项： #

• 请根据clients集合的实际字段名调整$project中client对象的字段（比如客户名称字段是fullName，则改为fullName: "$clientInfo.fullName"）。
• 若存在clientId为空或在clients集合中不存在的情况，$lookup会返回空数组，可添加$match过滤无效数据，或在$project中设置默认值。

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