mongodb - 多组 - 数组内每个值的平均值,没有来自另一个文档的重复
问题描述
为了更好地理解,我添加了三个 JSON 示例数据:
{
"_id" : ObjectId("5b961bdfd6588fed3fd8e2a9"),
"claim" : ObjectId("5b927bd9c725181a3dc9107e"),
"user" : ObjectId("5b869a5d0d56de9d788edb7e"),
"comprisedPhrase" : [
{
"phrase" : "ABC",
"like" : 1,
"dislike" : 0
},
{
"phrase" : "DEF",
"like" : 0,
"dislike" : 1
},
{
"phrase" : "GHI",
"like" : 1,
"dislike" : 0
}
],
},
{
"_id" : ObjectId("5b961bdfd6588fed3fd8e2a9"),
"claim" : ObjectId("5b927bd9c725181a3dc9107e"),
"user" : ObjectId("5b869a5d0d56de9d788edb7a”),
"comprisedPhrase" : [
{
"phrase" : "ABC",
"like" : 1,
"dislike" : 0
},
{
"phrase" : "DEF",
"like" : 0,
"dislike" : 1
},
{
"phrase" : "GHI",
"like" : 1,
"dislike" : 0
}
],
},
{
"_id" : ObjectId("5b961bdfd6588fed3fd8e2a9"),
"claim" : ObjectId("5b927bd9c725181a3dc9107a”),
"user" : ObjectId("5b869a5d0d56de9d788edb7a”),
"comprisedPhrase" : [
{
"phrase" : "ABC",
"like" : 0,
"dislike" : 1
},
{
"phrase" : "DEF",
"like" : 1,
"dislike" : 0
},
{
"phrase" : "GHI",
"like" : 1,
"dislike" : 0
},
{
"phrase" : "JKL",
"like" : 0,
"dislike" : 0
}
],
}
预期输出:
/* 1 */
{
"_id" : ObjectId("5b927bd9c725181a3dc9107e"), // Claim id
"comprisedPhrase" : [
{
"phrase" : "ABC",
"totalCount" : 2.0,
"sumLike" : 2,
"sumDislike" : 0
},
{
"phrase" : "DEF",
"totalCount" : 2.0,
"sumLike" : 0,
"sumDislike" : 2
},
{
"phrase" : "GHI",
"totalCount" : 2.0,
"sumLike" : 2,
"sumDislike" : 0
}
],
"totalCount" : 2.0
}
/* 2 */
{
"_id" : ObjectId("5b927bd9c725181a3dc9107a"),
"comprisedPhrase" : [
{
"phrase" : "ABC",
"totalCount" : 1.0,
"sumLike" : 0,
"sumDislike" : 1
},
{
"phrase" : "DEF",
"totalCount" : 1.0,
"sumLike" : 1,
"sumDislike" : 0
},
{
"phrase" : "GHI",
"totalCount" : 1.0,
"sumLike" : 1,
"sumDislike" : 0
},
{
"phrase" : "JKL",
"totalCount" : 1.0,
"sumLike" : 0,
"sumDislike" : 0
}
],
"totalCount" : 1.0
}
什么,我到目前为止尝试过:
db.getCollection(‘anydb’).aggregate([{
{
"$unwind": "$comprisedPhrase"
},
{
"$group" : {
_id: "$comprisedPhrase.phrase",
claimId: { "$first" : "$claim._id"},
totalCount: { "$sum": 1 },
sumLike : {"$sum" : "$comprisedPhrase.like"},
sumDislike : {"$sum" : "$comprisedPhrase.dislike"}
}
},{
"$group" : {
_id: "$claimId",
comprisedPhrase: { "$push" : { phrase: "$_id", totalCount: "$totalCount", sumLike: "$sumLike", sumDislike: "$sumDislike" }},
totalCount: { "$sum": 1 }
}
}
])
但是,这也会从不同的索赔中计算 ABC。这是显而易见的。
我只想按claim
id 分组。对于每个声明,都有单词列表。这些单词列表应该给我like
and dislike
or 平均 the like
and dislike
with inside的总和totalCount
。
注意:MongoDB V3.2.18
解决方案
您可以尝试以下聚合
db.collection.aggregate([
{ "$unwind": "$comprisedPhrase" },
{ "$group": {
"_id": { "phrase": "$comprisedPhrase.phrase", "claimId": "$claim" },
"sumLike": { "$sum": "$comprisedPhrase.like" },
"sumDislike": { "$sum": "$comprisedPhrase.dislike" },
"count": { "$sum": 1 }
}},
{ "$group": {
"_id": "$_id.claimId",
"totalCount": { "$first": "$count" },
"comprisedPhrase": {
"$push": {
"phrase": "$_id.phrase",
"sumLike": "$sumLike",
"sumDislike": "$sumDislike",
"totalCount": "$count"
}
}
}}
])
推荐阅读
- javascript - 如何从 JavaScript 运行 SQL 查询
- javascript - 有没有办法以 React 状态存储文件?
- linkedin - LinkedIn api v2 serviceErrorCode:65603 消息:提供了多个访问令牌
- ios - 如何更改 iOS 版 Firebase Analytics 中的最小会话持续时间值?
- python-3.x - 使用 PyCrypto 实现 El Gamal 加密-解密的 Python 实现溢出错误
- javascript - 当用户关闭弹出窗口时,保存位于弹出窗口中的复选框的值
- c# - 在 C#/LINQ 中将实体模型与 DTO 模型进行比较的最有效方法
- c++ - 使用 sfinae 挑选出首选的可变参数构造函数
- docker - 无法更改 production.ini 文件
- watson-assistant - 有没有办法使用 JSON 编辑器或 SPEL 访问 Watson Assistant 中的实体值元数据