如何计算mongodb聚合中数组内的统计信息?

ahy6op9u  于 2022-12-29  发布在  Go
关注(0)|答案(1)|浏览(148)

我正在Mongodb上构建一个简单的调查应用程序,最难的部分是使用Mongodb的聚合框架获得统计数据。
下面是一个提交调查答案的例子。answers属性是一个包含每个问题答案的数组。

  1. {
  2. uid:'xxxx',
  3. surveyId:'xxxxx',
  4. answers:[
  5. { answer: A }, // question 1 ‘s answer
  6. { answer: B }, // question 2 ‘s answer
  7. { answer: C } // question 3 ‘s answer
  8. ]
  9. }

我想得到的最后结果是了解调查的总体统计数字。

  1. Question 1:
  2. A 50% B 40% C 10%
  3. Question 2:
  4. A 60% B 40% C 0%

mongodb聚合的棘手之处在于如何处理“数组”

bzzcjhmw

bzzcjhmw1#

一个简单的选项是$unwind$group

  1. db.collection.aggregate([
  2. {$unwind: {path: "$answers", includeArrayIndex: "index"}},
  3. {$group: {
  4. _id: {
  5. surveyId: "$surveyId",
  6. index: "$index"
  7. },
  8. A: {$sum: {$cond: [{$eq: ["$answers.answer", "A"]}, 1, 0]}},
  9. B: {$sum: {$cond: [{$eq: ["$answers.answer", "B"]}, 1, 0]}},
  10. C: {$sum: {$cond: [{$eq: ["$answers.answer", "C"]}, 1, 0]}},
  11. all: {$sum: 1}
  12. }},
  13. {$project: {
  14. A: {$multiply: [{$divide: ["$A", "$all"]}, 100]},
  15. B: {$multiply: [{$divide: ["$B", "$all"]}, 100]},
  16. C: {$multiply: [{$divide: ["$C", "$all"]}, 100]},
  17. question: {$add: ["$_id.index", 1]},
  18. surveyId: "$_id.surveyId",
  19. _id: 0
  20. }}
  21. ])

了解它在playground example上的工作原理
或者,更通用的方法可以使用$group两次,而不知道每个问题的答案选项:

  1. db.collection.aggregate([
  2. {$unwind: {path: "$answers", includeArrayIndex: "index"}},
  3. {$group: {
  4. _id: {
  5. surveyId: "$surveyId",
  6. index: "$index",
  7. res: "$answers.answer"
  8. },
  9. count: {$sum: 1}
  10. }},
  11. {$group: {
  12. _id: {
  13. surveyId: "$_id.surveyId",
  14. index: "$_id.index"
  15. },
  16. data: {$push: {answer: "$_id.res", count: "$count"}},
  17. all: {$sum: "$count"}
  18. }},
  19. {$project: {
  20. data: {
  21. $map: {
  22. input: "$data",
  23. in: {
  24. answer: "$$this.answer",
  25. percent: {$multiply: [{$divide: ["$$this.count", "$all"]}, 100]}
  26. }
  27. }
  28. },
  29. question: {$add: ["$_id.index", 1]},
  30. surveyId: "$_id.surveyId",
  31. _id: 0
  32. }}
  33. ])

了解它在playground example上的工作原理

展开查看全部

相关问题