Mongo聚合：返回数组值的总平均值

Question

我在 mongodb 中有这些数据。

{ 
  "name": "FooBar",
  "__v": 0,
  "user_rating": [
  {
    "date": "2017-06-02T16:19:32.002Z",
    "user_rating": 5,
  },
  {
    "date": "2017-06-02T16:19:46.803Z",
    "user_rating": 3,
  },
  {
    "date": "2017-06-02T16:20:01.244Z",
    "user_rating": 5,
  },
  {
    "date": "2017-06-02T16:15:54.673Z",
    "user_rating": 3,
  },
  {
    "date": "2017-06-02T16:53:42.489Z",
    "user_rating": 5,
  }
 ]
}

我需要筛选并显示店铺名称、平均评分、总评分数量以及5星评分数量、4星评分数量等信息。

这是我的期望结果：

[
  {
    "_id":"FooBar",
    "countRating":5,
    "averageRating":4.2,
    "ratings": [
       {"num":3,"count":2},
       {"num":5,"count":3}
     ]
  }
 ]

然而，我似乎无法获取averageRating - 相反，我得到了每个评级的两个组合总数的平均值。

这是我的Mongo查询：

 db.collection.aggregate([
      {
        $match: {"name": "FooBar"}
      },
      {
          $unwind: "$user_rating"
      },
      {
        $match: {"name": "FooBar"}
      },
        {
          $group: {
            _id: {
              "name": "$name",
              "rating": "$user_rating.user_rating"
            },
            averageRating: {$avg: "$user_rating.user_rating"},
            countRating: {$sum: 1}
        }
      },
      {
        $group: {
          _id: "$_id.name",
          countRating: {"$sum": "$totalRatings"},
          averageRating: {"$avg": "$averageRating"},
          "rating": {
            "$push": {
              "num": "$_id.rating",
              "count": "$totalRatings"
            }
          },
        }
      }]);

有人能帮助求出这里的总平均数吗？谢谢！！

- beddow

Answer 1

db.collection.aggregate([
      { $match: {"name": "FooBar"} },
      { $unwind: "$user_rating" },
      {
          $group: {
              _id : "$name",
              avg: {$avg: "$user_rating.user_rating" },
              ratings: {$push: "$user_rating.user_rating" }
          }
      },
      { $unwind: "$ratings" },
      { 
          $group: {
              _id: { name: "$_id", num: "$ratings", avg: "$avg" },
              count: { $sum: 1 }              
          }
      },
      {
          $group: {
              _id: { name: "$_id.name", avg: "$_id.avg" },
              ratings: { $push: { num: "$_id.num", count: "$count" }}
          }
      },
      { $project: {_id:0, name:"$_id.name", averageRating: "$_id.avg", ratings: 1}}
 ])

我在这里使用了额外的分组来计算总平均评分。然后我展开结果并将计算出的平均值传递到分组键中。

输出：

{
    "name" : "FooBar",
    "averageRating" : 4.2,
    "ratings" : [ 
        {
            "num" : 3,
            "count" : 2
        }, 
        {
            "num" : 5,
            "count" : 3
        }
    ]
}

- Sergey Berezovskiy

---

我该如何返回一个包含所有评分的数组？并且添加分页以限制返回的数量。@sergey-berezovskiy - O'Dane Brissett