javascript - 如何在 Mongoose 聚合中找到平均值

Question

我正在汇总来自 3 个集合的数据 -用户-

 [
{
"_id": "5bc425bb3e99c8269ce51faa",
"fname": "Akash",
"salary": 25000,
"age": 23,
"__v": 0
},
{
"_id": "5bc425e23e99c8269ce51fab",
"fname": "Aditya",
"salary": 20000,
"age": 28,
"__v": 0
}
]

余额 -

[
{
"_id": "5bc4349adcc885e047054466",
"fname": "Akash",
"balance": 4000
},
{
"_id": "5bc434ffdcc885e0470544c2",
"fname": "Aditya",
"balance": 8000
}
]

部门收藏-

[
{
"_id": "5bc46167b365918881aa3bbf",
"fname": "Akash",
"dept": "IT"
},
{
"_id": "5bc4620fb365918881aa3c1b",
"fname": "Apurva",
"dept": "Chemical"
}
]

我想将结果汇总为 -

[
{
"_id": {
"fname": "Aditya",
"balance": 8000,
"dept": "Medical",
"salary": 20000
},
"avg": 20000
},
{
"_id": {
"fname": "Apurva",
"balance": 1000,
"dept": "Chemical",
"salary": 22000
},
"avg": 22000
}
]

我的查询 -

User.aggregate([
    {
      $lookup: {
        from: "balance",
        localField: "fname",
        foreignField: "fname",
        as: "result"
      }
    },
    {
      $unwind: "$result"
    },
    {
      $lookup: {
        from: "dept",
        localField: "fname",
        foreignField: "fname",
        as: "deptresult"
      }
    },
    {
      $unwind: "$deptresult"
    },
    { $sort: { salary: -1 } },
    // { $limit: 1 },
    {
      $project: {
        fname: 1,
        salary: 1,
        "result.balance": 1,
        "deptresult.dept": 1,
        avg: { $avg: "$salary" }
      }
    }

它显示剩余结果，但现在显示计算的平均值。而不是 average ，它只是打印 salary 。请有人指导我。我正在使用 mongoose ORM。其余代码运行良好。

Answer 1

您可以使用以下聚合

User.aggregate([
 { "$facet": {
   "projection": [
     { "$lookup": {
       "from": "balance",
       "localField": "fname",
       "foreignField": "fname",
       "as": "result"
     }},
     { "$lookup": {
       "from": "dept",
       "localField": "fname",
       "foreignField": "fname",
       "as": "deptresult"
     }},
     { "$unwind": "$deptresult" },
     { "$unwind": "$result" },
     { "$sort": { salary: -1 } },
     { "$project": {
       "fname": 1,
       "salary": 1,
       "result.balance": 1,
       "deptresult.dept": 1
     }}
   ],
   "averageSalary": [
     { "$group": {
       "_id": null,
       "avg": { "$avg": "$salary" }
     }}
   ]
 }}
])