英文:
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
}
]
英文:
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
}
]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论