查询 MongoDB:使用 $avg 和 $unwind 在数组中进行聚合。

huangapple go评论49阅读模式
英文:

Query mongodb: aggregate with $avg and $unwind in array

问题

我需要一个查询来返回平均评分给我,所以我尝试运行这个查询:

db.EVALUATION.aggregate([
  {
    $match: {
      _id: 84820
    }
  },
  {
    $unwind: "$reviews"
  },
  {
    $group: {
      _id: "$_id",
      avgRating: { $avg: "$reviews.rating" }
    }
  }
])

在实际生活中,返回值应该是 avgRating: 1.1875(我在Python中测试过)。但是,我的返回值是 avgRating: 1。

示例:

{
  _id: 84820,
  avgRating: 1
}

我该如何修复这个问题?我不知道MongoDB是否会自动将结果四舍五入为1... 如果是这样,我该如何让它显示完整的小数?

sum = 0
counter = 0

for review in reviews:
   rating = review["rating"]  
   sum += rating 
   counter += 1

media = sum / counter

print(media)

文档:

{
  "_id": 84820,
  "name": "Boula",
  "location": {
    "city": "Paris"
  },
  "category": "restaurant",
  "description": "",
  "reviews": [
    {
      "wordsCount": 14,
      "rating": 0,
      "polarity": 10
    },
    {
      "wordsCount": 11,
      "rating": 0,
      "polarity": 5
    },
    {
      "wordsCount": 16,
      "rating": 0,
      "polarity": 5
    },
    {
      "wordsCount": 7,
      "rating": 0,
      "polarity": 10
    },
    {
      "wordsCount": 19,
      "rating": 0,
      "polarity": 10
    },
    {
      "wordsCount": 6,
      "rating": 0,
      "polarity": 10
    },
    {
      "wordsCount": 2,
      "rating": 0,
      "polarity": 5
    },
    {
      "wordsCount": 5,
      "rating": 0,
      "polarity": 5
    },
    {
      "wordsCount": 18,
      "rating": 0,
      "polarity": 5
    },
    {
      "wordsCount": 5,
      "rating": 0,
      "polarity": 5
    },
    {
      "wordsCount": 4,
      "rating": 0,
      "polarity": 10
    },
    {
      "wordsCount": 60,
      "rating": 5,
      "polarity": 10
    },
    {
      "wordsCount": 47,
      "rating": 2,
      "polarity": 5
    },
    {
      "wordsCount": 18,
      "rating": 3,
      "polarity": 5
    },
    {
      "wordsCount": 18,
      "rating": 4,
      "polarity": 5
    },
    {
      "wordsCount": 6,
      "rating": 5,
      "polarity": 5
    }
  ],
  "nbReviews": 16
}

我尝试了许多查询,比如:

db.EVALUATION.aggregate([
  {
    $match: {
      _id: 84820
    }
  },
  {
    $unwind: "$reviews"
  },
  {
    $group: {
      _id: "$_id",
      avgRating: { $avg: { $toDouble: "$reviews.rating" } }
    }
  }
])
db.EVALUATION.aggregate([
  {
    $match: {
      _id: 84820
    }
  },
  {
    $unwind: "$reviews"
  },
  {
    $group: {
      _id: "$_id",
      sumRatings: { $sum: { $convert: { input: "$reviews.rating", to: "decimal" } } },
      countRatings: { $sum: 1 }
    }
  },
  {
    $project: {
      avgRating: { $divide: [ "$sumRatings", "$countRatings" ] }
    }
  }
])
db.EVALUATION.aggregate([
  {
    $match: {
      _id: 84820
    }
  },
  {
    $unwind: "$reviews"
  },
  {
    $group: {
      _id: "$_id",
      ratings: {
        $push: {
          $convert: {
            input: "$reviews.rating",
            to: "decimal",
            onError: 0,
            onNull: 0
          }
        }
      }
    }
  },
  {
    $project: {
      avgRating: { $avg: "$ratings" }
    }
  }
])
英文:

I need a query to return the avgRating to me, so I'm trying to run this query:

db.EVALUATION.aggregate([
{
$match: {
_id: 84820
}
},
{
$unwind: "$reviews"
},
{
$group: {
_id: "$_id",
avgRating: { $avg: "$reviews.rating" }
}
}
])

In real life the return needs to be avgRating: 1.1875 (I tested this in python). But, my return is avgRating: 1

example:

{
_id: 84820,
avgRating: 1
}

How i can fix this? I don't know if mongoDB is automatically rounding the result to 1... If so, how can I make it display the full decimal?

----code python----

sum= 0
counter= 0
for review in reviews:
rating = review["rating"]  
sum += rating 
counter  += 1
media = sum/ counter
print(media)

Document:

{
"_id": 84820,
"name": "Boula",
"location": {
"city": "Paris",
},
"category": "restaurant",
"description": "",
"reviews": [
{
"wordsCount": 14,
"rating": 0,
"polarity": 10
},
{
"wordsCount": 11,
"rating": 0,
"polarity": 5
},
{
"wordsCount": 16,
"rating": 0,
"polarity": 5
},
{
"wordsCount": 7,
"rating": 0,
"polarity": 10
},
{
"wordsCount": 19,
"rating": 0,
"polarity": 10
},
{
"wordsCount": 6,
"rating": 0,
"polarity": 10
},
{
"wordsCount": 2,
"rating": 0,
"polarity": 5
},
{
"wordsCount": 5,
"rating": 0,
"polarity": 5
},
{
"wordsCount": 18,
"rating": 0,
"polarity": 5
},
{
"wordsCount": 5,
"rating": 0,
"polarity": 5
},
{
"wordsCount": 4,
"rating": 0,
"polarity": 10
},
{
"wordsCount": 60,
"rating": 5,
"polarity": 10
},
{
"wordsCount": 47,
"rating": 2,
"polarity": 5
},
{
"wordsCount": 18,
"rating": 3,
"polarity": 5
},
{
"wordsCount": 18,
"rating": 4,
"polarity": 5
},
{
"wordsCount": 6,
"rating": 5,
"polarity": 5
}
],
"nbReviews": 16
},

I try many query, like:

db.EVALUATION.aggregate([
{
$match: {
_id: 84820
}
},
{
$unwind: "$reviews"
},
{
$group: {
_id: "$_id",
avgRating: { $avg: { $toDouble: "$reviews.rating" } }
}
}
])
db.EVALUATION.aggregate([
{
$match: {
_id: 84820
}
},
{
$unwind: "$reviews"
},
{
$group: {
_id: "$_id",
sumRatings: { $sum: { $convert: { input: "$reviews.rating", to: "decimal" } } },
countRatings: { $sum: 1 }
}
},
{
$project: {
avgRating: { $divide: [ "$sumRatings", "$countRatings" ] }
}
}
])
db.EVALUATION.aggregate([
{
$match: {
_id: 84820
}
},
{
$unwind: "$reviews"
},
{
$group: {
_id: "$_id",
ratings: {
$push: {
$convert: {
input: "$reviews.rating",
to: "decimal",
onError: 0,
onNull: 0
}
}
}
}
},
{
$project: {
avgRating: { $avg: "$ratings" }
}
}
])

答案1

得分: 1

I think you may be overcomplicating this. $avg is probably sufficient, based on the documented behavior with arrays here. Sample command:

db.collection.aggregate([
  {
    $project: {
      _id: 1,
      avgRating: {
        $avg: "$reviews.rating"
      }
    }
  }
])

Yields this result:

[
  {
    "_id": 84820,
    "avgRating": 1.1875
  }
]

Playground demonstration here

英文:

I think you may be overcomplicating this. $avg is probably sufficient, based on the documented behavior with arrays here. Sample command:

db.collection.aggregate([
{
$project: {
_id: 1,
avgRating: {
$avg: "$reviews.rating"
}
}
}
])

Yields this result:

[
{
"_id": 84820,
"avgRating": 1.1875
}
]

Playground demonstration here

huangapple
  • 本文由 发表于 2023年6月9日 04:38:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/76435537.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定