如何查找然后聚合

我有一个集合，其中包含模式如下的文档。我想过滤/找出所有包含性别女性的文件，然后把脑力总分加起来。我尝试了下面的语句，它显示了一个无效的管道错误。

db['!all'].aggregate({ $and: [ {'GENDER' :  'F'} , {'DOB' : { $gte : 19400801, $lte : 20131231 }} ]  }, { $group : { _id : "$GENDER", totalscore : { $sum : "$BRAINSCORE" } } } )

图式:

{
"_id" : ObjectId("53f63fc8f2b643f6ebb8a1a9"),
"DOB" : 19690112,
"GENDER" : "F",
"BRAINSCORE" : 65
},
{
"_id" : ObjectId("53f63fc8f2b643f6ebb8a1a2"),
"DOB" : 19950116,
"GENDER" : "F",
"BRAINSCORE" : 44
},
{
"_id" : ObjectId("53f63fc8f2b643f6ebb8a902"),
"DOB" : 19430216,
"GENDER" : "M",
"BRAINSCORE" : 71
}
109287 次浏览
小开
最佳答案

You have to use $match:

db['!all'].aggregate([
{$match:
{'GENDER': 'F',
'DOB':
{ $gte: 19400801,
$lte: 20131231 } } },
{$group:
{_id: "$GENDER",
totalscore:{ $sum: "$BRAINSCORE"}}}
])

Outputs:

{ "_id" : "F", "totalscore" : 109 }
小开

Sample working query :

db.getCollection('NOTIF_EVENT_RESULT').aggregate([
{$match:
{'userId': {'$in' : ['user-900', 'user-1546']},
'criteria.operator': 'greater than', 'criteria.thresold' : '90', 'category' : 'capacity'}
},
{"$group" :  {_id : {userId:"$userId"}, "count" : { "$sum" : 1} } }
])
小开

Here is an answer if the DOB numbers needs to be converted to Date then compared. If not, a number or Date such as 1970 will be incorrectly $gte to 19400801 (you can try):

db['!all'].aggregate([
{
$addFields: {
"_temp_DOB": {
$dateFromString: {
dateString: {$toString: {$toLong: "$DOB"}},
format: "%Y%m%d"
}
}
}
},
{
$match: {
'GENDER': 'F',
'_temp_DOB': { $gte: new Date("1940-08-01"),
$lte: new Date("2013-12-31") }
}
},
{
$group: {
_id: "$GENDER",
totalscore: { $sum: "$BRAINSCORE" }
}
}
])

Outputs:

{ "_id" : "F", "totalscore" : 109 }
小开

In addition to Enrique's answer,

If you want to aggregate using a user's id, (which is a mongoose objectID), you need to cast your query id (which is of type string) to mongoose objectID. Thus:

const userId = mongoose.Types.ObjectId(user_id).

Else, $match it will return an empty array.


提交答案