英文:
MongoDB aggregation to group by multiple fields
问题
以下是您要翻译的内容：
我在我的集合中有以下数据
{
“name": “test”,
"data": {
“statusOne”: “enabled”,
“statusTwo”: “active”
}
}
{
“name": “test”,
"data": {
“statusOne”: “disabled”,
“statusTwo”: “active”
}
}
{
“name": “another-test”,
"data": {
“statusOne”: “disabled”,
“statusTwo”: “active”
}
}
如何编写聚合查询以显示以下数据。需要按名称，statusOne和statusTwo进行分组。但首先基于名称字段对结果进行分隔。然后计算statusOne和statusTwo的出现次数。这两个结果都需要放入相同的输出字段"data"中。
“output”: [
{
“name”: “test”,
"data": [
{
“status”: “active”,
“count”: 2
},
{
“status”: “disabled”,
”count”: 1
},
{
“status”: “enabled”,
”count”: 1
}
]
},
{
“name”: “another-test”,
"data": [
{
“status”: “active”,
”count”: 1
},
{
“status”: “disabled”,
”count”: 1
}
]
}
]
尝试使用如下所述的分组序列，但没有成功
[
{
$group: {
_id: {
appName: "$name”,
cs: "$data.statusOne”,
ps: "$data.statusTwo,
},
total: {
$sum: 1,
},
},
},
{
$group: {
_id: "$_id.name”,
total: { $sum: "$total" },
ps: {
$addToSet: {
name: "$_id.ps",
count: "$total",
},
},
cs: {
$addToSet: {
name: "$_id.cs",
count: "$total",
},
},
},
},
{
$project: {
_id: 0,
appName: "$_id",
items: {
$concatArrays: ["$ps", "$cs"],
},
},
},
]
请注意，上述代码示例中的引号可能需要根据您的编程环境进行调整。
英文:
I have following data in my collection
{
“name": “test”,
"data": {
“statusOne”: “enabled”,
“statusTwo”: “active”
}
}
{
“name": “test”,
"data": {
“statusOne”: “disabled”,
“statusTwo”: “active”
}
}
{
“name": “another-test”,
"data": {
“statusOne”: “disabled”,
“statusTwo”: “active”
}
}
How to write an aggregation query to display the data like below. Need to group by name, statusOne, statusTwo. But segregate the result first based on name field. Then calculate the number of occurrences of statusOne and statusTwo. Both the results need to go into the same output field "data"
“output”: [
{
“name”: “test”,
"data": [
{
“status”: “active”,
“count”: 2
},
{
“status”: “disabled”,
”count”: 1
},
{
“status”: “enabled”,
”count”: 1
}
]
},
{
“name”: “another-test”,
"data": [
{
“status”: “active”,
”count”: 1
},
{
“status”: “disabled”,
”count”: 1
}
]
}
]
Tried to use group by sequence as mentioned here but no luck
[
{
$group: {
_id: {
appName: "$name”,
cs: "$data.statusOne”,
ps: "$data.statusTwo,
},
total: {
$sum: 1,
},
},
},
{
$group: {
_id: "$_id.name”,
total: { $sum: "$total" },
ps: {
$addToSet: {
name: "$_id.ps",
count: "$total",
},
},
cs: {
$addToSet: {
name: "$_id.cs",
count: "$total",
},
},
},
},
{
$project: {
_id: 0,
appName: "$_id",
items: {
$concatArrays: ["$ps", "$cs"],
},
},
},
]
答案1
得分: 2
db.collection('表名').aggregate([
{
$group: {
_id: { name: "$name", status: "$data.statusOne" },
count: { $sum: 1 }
}
},
{
$group: {
_id: "$_id.name",
data: {
$push: {
status: "$_id.status",
count: "$count"
}
}
}
},
{
$project: {
_id: 0,
name: "$_id",
data: 1
}
}
])
英文:
db.collection('tablename').aggregate([
{
$group: {
_id: { name: "$name", status: "$data.statusOne" },
count: { $sum: 1 }
}
},
{
$group: {
_id: "$_id.name",
data: {
$push: {
status: "$_id.status",
count: "$count"
}
}
}
},
{
$project: {
_id: 0,
name: "$_id",
data: 1
}
}
])
use this and change the collection name .
答案2
得分: 1
这是一个通用的解决方案：
db.foo.aggregate([
{$project: {
name: true,
/*
我们可以通过$objectToArray的输出进行更高级的操作，例如仅获取statusOne和statusTwo：
Z: {$filter: {
input: {$objectToArray: "$data"},
cond: {$in: ['$$this.k', ['statusOne','statusTwo']]}
}}
或者仅查找以'status'开头的键：
Z: {$filter: {
input: {$objectToArray: "$data"},
cond: {$eq: ['status', {$substr:['$$this.k',0,6]}]}
}}
现在让我们简单点，获取整个内容而不进行过滤。
*/
Z: {$objectToArray: "$data"}
}}
,{$unwind: "$Z"}
// 现在我们有了这样的文档：
//{
// _id: ObjectId("64d651283d8bc34d3928366d"),
// name: 'another-test',
// Z: {
// k: 'statusOne',
// v: 'disabled'
// }
//}
// 现在只是分组和重新组织的问题：
,{$group: {_id: {"name":"$name", "v":"$Z.v"}, N:{$sum:1}}}
,{$group: {_id: "$_id.name", data: {$push: {status:"$_id.v", count:"$N"}} }}
]);
产生的结果为：
{
_id: 'test',
data: [
{
status: 'active',
count: 2
},
{
status: 'enabled',
count: 1
},
{
status: 'disabled',
count: 1
}
]
}
{
_id: 'another-test',
data: [
{
status: 'disabled',
count: 1
},
{
status: 'active',
count: 1
}
]
}
如果有成千上万的测试，我们不想使用$unwind怎么办？
在管道中使用$unwind时，必须小心。如果每个测试的平均状态数量变大（比如100），那么在管道中将会有大量文档。以下是一种利用$reduce的替代解决方案：
c=db.foo.aggregate([
{$project: {
name: true,
Z: {$objectToArray: "$data"}
}}
/*
不使用$unwind并使用$group-$sum来计数，让我们自己来做。
我们将使用新的Z覆盖旧的Z。
此外，由于这是一个中间步骤，使用较短的变量名称以增加清晰度。
这个if-then-else构造基本上是这样的：
对于每种状态类型，status_type_count += 1
这允许输入具有相同类型的多个状态，例如：
"name": "test",
"data": {
"statusOne": "enabled",
"statusTwo": "enabled"
}
在MQL中，$reduce循环中，我们不说
object.key = object.key + 1
而是说：
{$mergeObjects: [ "$$value", {key: {$add:["$$value.key",1]}} ]}
*/
,{$addFields: {Z: {$reduce: {
input: "$Z",
initialValue: {"A":0,"E":0,"D":0},
in: {$cond: {
if: {$eq:["$$this.v","active"]},
then: {$mergeObjects: [ "$$value", {"A": {$add:["$$value.A",1]}} ]},
else: {$cond: {
if: {$eq:["$$this.v","disabled"]},
then: {$mergeObjects: [ "$$value", {"D": {$add:["$$value.D",1]}} ]},
else: {$cond: {
if: {$eq:["$$this.v","enabled"]},
then: {$mergeObjects: [ "$$value", {"E": {$add:["$$value.E",1]}} ]},
else: "$$value"
}}
}}
}}
}}
}}
// 将名称组合在一起并收集计数：
,{$group: {
_id: "$name", X: {$push: "$Z"}
}}
// 现在，再次运行$reduce以汇总计数并恢复大变量名称：
,{$project: {data: {$reduce: {
input: "$X",
initialValue: {"active":0,"enabled":0,"disabled":0},
in: {"active": {$add:["$$value.active","$$this.A"]},
"disabled": {$add:["$$value.disabled","$$this.D"]},
"enabled": {$add:["$$value.enabled","$$this.E"]}
}
}}
}}
// 此时，我们“完成”了
<details>
<summary>英文:</summary>
Here is a generalized solution:
```javascript
db.foo.aggregate([
{$project: {
name: true,
/*
We could get fancy with the output of $objectToArray, like
only going after statusOne and statusTwo:
Z: {$filter: {
input: {$objectToArray: "$data"},
cond: {$in: ['$$this.k', ['statusOne','statusTwo']]}
}}
Or only looking for keys that start with 'status':
Z: {$filter: {
input: {$objectToArray: "$data"},
cond: {$eq: ['status', {$substr:['$$this.k',0,6]}]}
}}
Let's keep it simple now and take the whole thing
without any filtering.
*/
Z: {$objectToArray: "$data"}
}}
,{$unwind: "$Z"}
// Now we have docs like this:
//{
// _id: ObjectId("64d651283d8bc34d3928366d"),
// name: 'another-test',
// Z: {
// k: 'statusOne',
// v: 'disabled'
// }
//}
// Now it is just a matter of grouping and reorganizing:
,{$group: {_id: {"name":"$name", "v":"$Z.v"}, N:{$sum:1}}}
,{$group: {_id: "$_id.name", data: {$push: {status:"$_id.v", count:"$N"}} }}
]);
yields
{
_id: 'test',
data: [
{
status: 'active',
count: 2
},
{
status: 'enabled',
count: 1
},
{
status: 'disabled',
count: 1
}
]
}
{
_id: 'another-test',
data: [
{
status: 'disabled',
count: 1
},
{
status: 'active',
count: 1
}
]
}
What if there are thousands of tests and we don't want to $unwind?
You must be careful when throwing $unwind into a pipeline. If the average number of statuses per test gets large (say, 100) then there will be a LOT of docs in the pipeline. Below is an alternate solution that exploits $reduce:
c=db.foo.aggregate([
{$project: {
name: true,
Z: {$objectToArray: "$data"}
}}
/*
Instead of $unwind and using $group-$sum to count things, let's
do it ourselves. We will overwrite Z with a new Z.
Also, since this is an iterim step, use shorter variable names for
clarity.
This if-then-else construction basically says:
For each status type, status_type_count += 1
This permits the input to have more than 1 status of the same type, e.g.
"name": "test",
"data": {
"statusOne": "enabled",
"statusTwo": "enabled"
}
In MQL in a $reduce loop, we don't say
object.key = object.key + 1
Instead we say:
{$mergeObjects: [ "$$value", {key: {$add:["$$value.key",1]}} ]}
*/
,{$addFields: {Z: {$reduce: {
input: "$Z",
initialValue: {"A":0,"E":0,"D":0},
in: {$cond: {
if: {$eq:["$$this.v","active"]},
then: {$mergeObjects: [ "$$value", {"A": {$add:["$$value.A",1]}} ]},
else: {$cond: {
if: {$eq:["$$this.v","disabled"]},
then: {$mergeObjects: [ "$$value", {"D": {$add:["$$value.D",1]}} ]},
else: {$cond: {
if: {$eq:["$$this.v","enabled"]},
then: {$mergeObjects: [ "$$value", {"E": {$add:["$$value.E",1]}} ]},
else: "$$value"
}}
}}
}}
}}
}}
// Bring the names together and collect the counts:
,{$group: {
_id: "$name", X: {$push: "$Z"}
}}
// Now, run a $reduce again to sum the counts AND put back the
// big variable names:
,{$project: {data: {$reduce: {
input: "$X",
initialValue: {"active":0,"enabled":0,"disabled":0},
in: {"active": {$add:["$$value.active","$$this.A"]},
"disabled": {$add:["$$value.disabled","$$this.D"]},
"enabled": {$add:["$$value.enabled","$$this.E"]}
}
}}
}}
// At this point we are "done" information-wise but the OP was
// looking for an array of status as an RVAL not a key (e.g. "A")
// so post-process:
,{$project: {
_id:0,
name:"$_id",
data: {$map: {
input: {$objectToArray: "$data"},
in: {
"status":"$$this.k",
"count":"$$this.v",
}
}}
}}
]);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论