英文:
MongoDB Aggregation for building conditional sums
问题
让我们假设我在我的MongoDB中有一些用户文档。每个用户都有一个type
(整数)和presence
天数(布尔值)。
我的文档架构
{
"type": NumberInt(), // 1|2|3
"presence": {
"day1": true,
"day2": false,
"day3": true
}
}
我想要计算每种用户类型的出勤天数,以便可视化它们在线图中的出勤情况。所需的输出应如下所示。一个数组中包含三个表示类型的对象。每个对象有一系列数据。它们代表了在指定日期出勤的特定类型用户的总数。
所需结果
[{
"name": "Type1",
"series": [{
"name": "Day1",
"value": 5 // 计数(type==1 && d1==true)
}, {
"name": "Day2",
"value": 8 // 计数(type==1 && d2==true)
}, {
"name": "Day3",
...
}]
}, {
"name": "Type2",
"series": [{
"name": "Day1",
"value": 10 // 计数(type==2 && d1==true)
}, {}, {}]
}, {
"name": "Type3",
"series": [{}, {}, {}]
}, {
"name": "AllCombined",
"series": [{}, {}, {}]
}]
问题
我如何使用MongoDB聚合来实现这个目标?我尝试过$match
、$sort
、$project
和$sum
,但无法弄清楚。提前感谢您的帮助!
请求示例:用户集合
{ "_id": ..., "type": 1, "presence": { "d1": true, "d2": true, "d3": false } }
{ "_id": ..., "type": 2, "presence": { "d1": false, "d2": true, "d3": false } }
{ "_id": ..., "type": 3, "presence": { "d1": true, "d2": false, "d3": true } }
{ "_id": ..., "type": 1, "presence": { "d1": true, "d2": false, "d3": false } }
英文:
Let's pretend I have some user documents in my MongoDB. Every user has a type
(int) and presence
days (boolean).
my documents schema
{
type: NumberInt() // 1|2|3
presence: {
day1: true,
day2: false,
day3: true
}
I want to count the precence days for each type of user to visualize their precence in a line chart. The needed output has to look like this. Three objects in an array representing the types. Every object has series. They represent the sum of users of a specific type present at the named day.
wanted result
[{
name: "Type1"
series: [{
name: "Day1",
value: 5, // count (type==1 && d1==true)
}, {
name: "Day2",
value: 8, // count (type==1 && d2==true)
}, {
name: "Day3",
...
}]
}, {
name: "Type2"
series: [{
name: "Day1",
value: 10, // count (type==2 && d1==true)
}, {}, {}]
}, {
name: "Type3"
series: [{}, {}, {}]
}, {
name: "AllCombined"
series: [{}, {}, {}]
}]
Question
How can I achive that with mongodb aggregation? I played around with $match
, $sort
, $project
, $sum
but I couldn't figure it out. Thanks in advance
requested samples: user collection
{_id: ..., type: 1, presence: {d1:true, d2: true, d3: false}}
{_id: ..., type: 2, presence: {d1:false, d2: true, d3: false}}
{_id: ..., type: 3, presence: {d1:true, d2: false, d3: true}}
{_id: ..., type: 1, presence: {d1:true, d2: false, d3: false}}
答案1
得分: 1
你可以使用以下的聚合操作:
db.collection.aggregate([
{ "$group": {
"_id": "$type",
"d1": { "$sum": { "$cond": [{ "$eq": ["$presence.d1", true] }, 1, 0] }},
"d2": { "$sum": { "$cond": [{ "$eq": ["$presence.d2", true] }, 1, 0] }},
"d3": { "$sum": { "$cond": [{ "$eq": ["$presence.d3", true] }, 1, 0] }}
}},
{ "$project": {
"_id": 0,
"name": { "$concat": ["Type", { "$toString": "$_id" }] },
"series": [
{
"name": "Day1",
"value": "$d1"
},
{
"name": "Day2",
"value": "$d2"
},
{
"name": "Day3",
"value": "$d3"
}
]
}}
])
英文:
You can use below aggregation
db.collection.aggregate([
{ "$group": {
"_id": "$type",
"d1": { "$sum": { "$cond": [{ "$eq": ["$presence.d1", true] }, 1, 0] }},
"d2": { "$sum": { "$cond": [{ "$eq": ["$presence.d2", true] }, 1, 0] }},
"d3": { "$sum": { "$cond": [{ "$eq": ["$presence.d3", true] }, 1, 0] }}
}},
{ "$project": {
"_id": 0,
"name": { "$concat": ["Type", { "$toString": "$_id" }] },
"series": [
{
"name": "Day1",
"value": "$d1"
},
{
"name": "Day2",
"value": "$d2"
},
{
"name": "Day3",
"value": "$d3"
}
]
}}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论