Grouping With aggregation in MongoDB

Question

目前我在MongoDB中使用聚合。在我的收藏中有一个关于省和宗教的领域。我正在做这个

const data = await submit.aggregate([
{ "$group": { _id: { province: "$province" ,religion:"$religion"}, count: { $sum: 1 } } },
])

我的输出如下所示:

[
{ _id: { religion: 'a', province: 'aa' }, count: 1 },
{ _id: { religion: b, province: 'bb' }, count: 2 },
{ _id: { religion: 'c', province: 'bb'}, count: 2 },
{ _id: { religion: 'd', province: 'cc' }, count: 1 } 
]

期望输出:

[
{ _id: { religion: 'a ' }, count: 1 },
{ _id: { religion: 'a' }, count: 1 },
{ _id: { religion: null }, count: 6 },
{ _id: { religion: 'c' }, count: 1 },
{ _id: { religion: 'd' }, count: 2 },
{ _id: { religion: 'e' }, count: 6 },
{ _id: { religion: 'f' }, count: 15 },
{ _id: { religion: 'g' }, count: 2 },


] [
{ _id: { province: 'aa' }, count: 19 },
{ _id: { province: 'bb' }, count: 2 },
{ _id: { province: 'cc' }, count: 21 },

]

Answer 1

您同时寻找2个不同的$group—这正是$facet的作用。把$facet想象成"多组"。给定一个类似于下面的输入集:

{ religion: 'a', province: 'aa' },
{ religion: 'b', province: 'aa' },
{ religion: 'c', province: 'aa' },
{ religion: 'c', province: 'bb' },
{ religion: 'd', province: 'bb' },
{ religion: 'e', province: 'cc' },      
{ religion: 'f', province: 'aa' },
{ religion: 'f', province: 'aa' },
{ religion: 'f', province: 'aa' },
{ religion: 'f', province: 'cc' }

那么这个管道:

db.foo.aggregate([
{$facet: {
"by_religion": [
{$group: {_id: '$religion', N:{$sum:1}}}
],
"by_province": [
{$group: {_id: '$province', N:{$sum:1}}}
],
}}  
]);

产生如下输出:

{
"by_religion" : [
{
"_id" : "b",
"N" : 1
},
{
"_id" : "e",
"N" : 1
},
{
"_id" : "d",
"N" : 1
},
{
"_id" : "a",
"N" : 1
},
{
"_id" : "f",
"N" : 4
},
{
"_id" : "c",
"N" : 2
}
],
"by_province" : [
{
"_id" : "bb",
"N" : 2
},
{
"_id" : "cc",
"N" : 2
},
{
"_id" : "aa",
"N" : 6
}
]
}

OP试图通过做一些数据作为lval的处理来进一步优化输出，尽管这通常被认为是一个糟糕的设计实践，但它具有某些有用的应用程序。在$facet之后添加这个阶段:

,{$project: {
// Reading this from insider-out:                                             
// We use $map to turn the array of objects:                                  
//   [ {_id:'d',N:1},{_id:'f',N:4}, ... ]                                     
// into an array of K-v pairs (array of array):                               
//   [ ['d',1] , ['f',4] , ...  ]                                             
// That sets us up for $arrayToObject which will take                         
// that array of arrays and turn it into an object:                           
//   {'d':1, 'f':4, ... } 
// The target field name is the same as the input so
// we are simply overwriting the field.        
"by_religion": {$arrayToObject: {$map: {
input: '$by_religion',
in: [ '$$this._id', '$$this.N' ]
}}
},
"by_province": {$arrayToObject: {$map: {
input: '$by_province',
in: [ '$$this._id', '$$this.N' ]
}}
}
}}

产量:

{
"by_religion" : {
"d" : 1,
"b" : 1,
"c" : 2,
"f" : 4,
"a" : 1,
"e" : 1
},
"by_province" : {
"bb" : 2,
"cc" : 2,
"aa" : 6
}
}

lval/rval的一个变体使用这个$project而不是上面的那个:

,{$project: {
"by_religion": {$map: {
input: '$by_religion',
in: {$arrayToObject: [ [{k:'$$this._id',v:'$$this.N'}] ]}
}},
"by_province": {$map: {
input: '$by_province',
in: {$arrayToObject: [ [{k:'$$this._id',v:'$$this.N'}] ]}
}},
}}

生成数组:

{
"by_religion" : [
{"b" : 1},
{"c" : 2},
{"a" : 1},
{"f" : 4},
{"d" : 1},
{"e" : 1}
],
"by_province" : [
{"cc" : 2},
{"aa" : 6},
{"bb" : 2}
]
}