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

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

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

问题

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

  1. db.EVALUATION.aggregate([
  2.   {
  3.     $match: {
  4.       _id: 84820
  5.     }
  6.   },
  7.   {
  8.     $unwind: "$reviews"
  9.   },
  10.   {
  11.     $group: {
  12.       _id: "$_id",
  13.       avgRating: { $avg: "$reviews.rating" }
  14.     }
  15.   }
  16. ])

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

示例：

  1. {
  2.   _id: 84820,
  3.   avgRating: 1
  4. }

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

  1. sum = 0
  2. counter = 0
  4. for review in reviews:
  5.    rating = review["rating"]  
  6.    sum += rating 
  7.    counter += 1
  9. media = sum / counter
  11. print(media)

文档：

  1. {
  2.   "_id": 84820,
  3.   "name": "Boula",
  4.   "location": {
  5.     "city": "Paris"
  6.   },
  7.   "category": "restaurant",
  8.   "description": "",
  9.   "reviews": [
  10.     {
  11.       "wordsCount": 14,
  12.       "rating": 0,
  13.       "polarity": 10
  14.     },
  15.     {
  16.       "wordsCount": 11,
  17.       "rating": 0,
  18.       "polarity": 5
  19.     },
  20.     {
  21.       "wordsCount": 16,
  22.       "rating": 0,
  23.       "polarity": 5
  24.     },
  25.     {
  26.       "wordsCount": 7,
  27.       "rating": 0,
  28.       "polarity": 10
  29.     },
  30.     {
  31.       "wordsCount": 19,
  32.       "rating": 0,
  33.       "polarity": 10
  34.     },
  35.     {
  36.       "wordsCount": 6,
  37.       "rating": 0,
  38.       "polarity": 10
  39.     },
  40.     {
  41.       "wordsCount": 2,
  42.       "rating": 0,
  43.       "polarity": 5
  44.     },
  45.     {
  46.       "wordsCount": 5,
  47.       "rating": 0,
  48.       "polarity": 5
  49.     },
  50.     {
  51.       "wordsCount": 18,
  52.       "rating": 0,
  53.       "polarity": 5
  54.     },
  55.     {
  56.       "wordsCount": 5,
  57.       "rating": 0,
  58.       "polarity": 5
  59.     },
  60.     {
  61.       "wordsCount": 4,
  62.       "rating": 0,
  63.       "polarity": 10
  64.     },
  65.     {
  66.       "wordsCount": 60,
  67.       "rating": 5,
  68.       "polarity": 10
  69.     },
  70.     {
  71.       "wordsCount": 47,
  72.       "rating": 2,
  73.       "polarity": 5
  74.     },
  75.     {
  76.       "wordsCount": 18,
  77.       "rating": 3,
  78.       "polarity": 5
  79.     },
  80.     {
  81.       "wordsCount": 18,
  82.       "rating": 4,
  83.       "polarity": 5
  84.     },
  85.     {
  86.       "wordsCount": 6,
  87.       "rating": 5,
  88.       "polarity": 5
  89.     }
  90.   ],
  91.   "nbReviews": 16
  92. }

我尝试了许多查询，比如：

  1. db.EVALUATION.aggregate([
  2.   {
  3.     $match: {
  4.       _id: 84820
  5.     }
  6.   },
  7.   {
  8.     $unwind: "$reviews"
  9.   },
  10.   {
  11.     $group: {
  12.       _id: "$_id",
  13.       avgRating: { $avg: { $toDouble: "$reviews.rating" } }
  14.     }
  15.   }
  16. ])
  1. db.EVALUATION.aggregate([
  2.   {
  3.     $match: {
  4.       _id: 84820
  5.     }
  6.   },
  7.   {
  8.     $unwind: "$reviews"
  9.   },
  10.   {
  11.     $group: {
  12.       _id: "$_id",
  13.       sumRatings: { $sum: { $convert: { input: "$reviews.rating", to: "decimal" } } },
  14.       countRatings: { $sum: 1 }
  15.     }
  16.   },
  17.   {
  18.     $project: {
  19.       avgRating: { $divide: [ "$sumRatings", "$countRatings" ] }
  20.     }
  21.   }
  22. ])
  1. db.EVALUATION.aggregate([
  2.   {
  3.     $match: {
  4.       _id: 84820
  5.     }
  6.   },
  7.   {
  8.     $unwind: "$reviews"
  9.   },
  10.   {
  11.     $group: {
  12.       _id: "$_id",
  13.       ratings: {
  14.         $push: {
  15.           $convert: {
  16.             input: "$reviews.rating",
  17.             to: "decimal",
  18.             onError: 0,
  19.             onNull: 0
  20.           }
  21.         }
  22.       }
  23.     }
  24.   },
  25.   {
  26.     $project: {
  27.       avgRating: { $avg: "$ratings" }
  28.     }
  29.   }
  30. ])
英文:

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

  1. db.EVALUATION.aggregate([
  2. {
  3. $match: {
  4. _id: 84820
  5. }
  6. },
  7. {
  8. $unwind: "$reviews"
  9. },
  10. {
  11. $group: {
  12. _id: "$_id",
  13. avgRating: { $avg: "$reviews.rating" }
  14. }
  15. }
  16. ])

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

example:

  1. {
  2. _id: 84820,
  3. avgRating: 1
  4. }

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----

  1. sum= 0
  2. counter= 0
  3. for review in reviews:
  4. rating = review["rating"]  
  5. sum += rating 
  6. counter  += 1
  7. media = sum/ counter
  8. print(media)

Document:

  1. {
  2. "_id": 84820,
  3. "name": "Boula",
  4. "location": {
  5. "city": "Paris",
  6. },
  7. "category": "restaurant",
  8. "description": "",
  9. "reviews": [
  10. {
  11. "wordsCount": 14,
  12. "rating": 0,
  13. "polarity": 10
  14. },
  15. {
  16. "wordsCount": 11,
  17. "rating": 0,
  18. "polarity": 5
  19. },
  20. {
  21. "wordsCount": 16,
  22. "rating": 0,
  23. "polarity": 5
  24. },
  25. {
  26. "wordsCount": 7,
  27. "rating": 0,
  28. "polarity": 10
  29. },
  30. {
  31. "wordsCount": 19,
  32. "rating": 0,
  33. "polarity": 10
  34. },
  35. {
  36. "wordsCount": 6,
  37. "rating": 0,
  38. "polarity": 10
  39. },
  40. {
  41. "wordsCount": 2,
  42. "rating": 0,
  43. "polarity": 5
  44. },
  45. {
  46. "wordsCount": 5,
  47. "rating": 0,
  48. "polarity": 5
  49. },
  50. {
  51. "wordsCount": 18,
  52. "rating": 0,
  53. "polarity": 5
  54. },
  55. {
  56. "wordsCount": 5,
  57. "rating": 0,
  58. "polarity": 5
  59. },
  60. {
  61. "wordsCount": 4,
  62. "rating": 0,
  63. "polarity": 10
  64. },
  65. {
  66. "wordsCount": 60,
  67. "rating": 5,
  68. "polarity": 10
  69. },
  70. {
  71. "wordsCount": 47,
  72. "rating": 2,
  73. "polarity": 5
  74. },
  75. {
  76. "wordsCount": 18,
  77. "rating": 3,
  78. "polarity": 5
  79. },
  80. {
  81. "wordsCount": 18,
  82. "rating": 4,
  83. "polarity": 5
  84. },
  85. {
  86. "wordsCount": 6,
  87. "rating": 5,
  88. "polarity": 5
  89. }
  90. ],
  91. "nbReviews": 16
  92. },

I try many query, like:

  1. db.EVALUATION.aggregate([
  2. {
  3. $match: {
  4. _id: 84820
  5. }
  6. },
  7. {
  8. $unwind: "$reviews"
  9. },
  10. {
  11. $group: {
  12. _id: "$_id",
  13. avgRating: { $avg: { $toDouble: "$reviews.rating" } }
  14. }
  15. }
  16. ])
  1. db.EVALUATION.aggregate([
  2. {
  3. $match: {
  4. _id: 84820
  5. }
  6. },
  7. {
  8. $unwind: "$reviews"
  9. },
  10. {
  11. $group: {
  12. _id: "$_id",
  13. sumRatings: { $sum: { $convert: { input: "$reviews.rating", to: "decimal" } } },
  14. countRatings: { $sum: 1 }
  15. }
  16. },
  17. {
  18. $project: {
  19. avgRating: { $divide: [ "$sumRatings", "$countRatings" ] }
  20. }
  21. }
  22. ])
  1. db.EVALUATION.aggregate([
  2. {
  3. $match: {
  4. _id: 84820
  5. }
  6. },
  7. {
  8. $unwind: "$reviews"
  9. },
  10. {
  11. $group: {
  12. _id: "$_id",
  13. ratings: {
  14. $push: {
  15. $convert: {
  16. input: "$reviews.rating",
  17. to: "decimal",
  18. onError: 0,
  19. onNull: 0
  20. }
  21. }
  22. }
  23. }
  24. },
  25. {
  26. $project: {
  27. avgRating: { $avg: "$ratings" }
  28. }
  29. }
  30. ])

答案1

得分: 1

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

  1. db.collection.aggregate([
  2.   {
  3.     $project: {
  4.       _id: 1,
  5.       avgRating: {
  6.         $avg: "$reviews.rating"
  7.       }
  8.     }
  9.   }
  10. ])

Yields this result:

  1. [
  2.   {
  3.     "_id": 84820,
  4.     "avgRating": 1.1875
  5.   }
  6. ]

Playground demonstration here

英文:

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

  1. db.collection.aggregate([
  2. {
  3. $project: {
  4. _id: 1,
  5. avgRating: {
  6. $avg: "$reviews.rating"
  7. }
  8. }
  9. }
  10. ])

Yields this result:

  1. [
  2. {
  3. "_id": 84820,
  4. "avgRating": 1.1875
  5. }
  6. ]

Playground demonstration here

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

发表评论

匿名网友

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

确定