I am having several records in a information table. In which infoId can be common for several users who are involved in that.Information Collection looks like:
{
_id:ObjectId(),
infoId:001,
userName:x,
designation:xx,
year:10
},
{
_id:ObjectId(),
infoId:001,
userName:a,
designation:aa,
year:10
},
{
_id:ObjectId(),
infoId:001,
userName:y,
designation:yy,
year:3
},
{
_id:ObjectId(),
infoId:002,
userName:x,
designation:xx,
year:7
},
{
_id:ObjectId(),
infoId:003,
userName:x,
designation:xx,
year:10
},
{
_id:ObjectId(),
infoId:001,
userName:c,
designation:cc,
year:10
}....
From the information table, I want to get the records based on grouping infoId. So I can use aggregate query to get this.
db.information.aggregate([$group:{_id:$infoId,users:{$push:"$$ROOT"},$sort:{infoId:-1}]);
I will get the result like :
[{
_id:003,
users:[
{
_id:ObjectId(),
infoId:003,
userName:x,
designation:xx,
year:10
}]
},
{
_id:002,
users:[{
_id:ObjectId(),
infoId:002,
userName:x,
designation:xx,
year:7
}]
},
{
_id:001,users:[
{
_id:ObjectId(),
infoId:001,
userName:x,
designation:xx,
year:10
},
{
_id:ObjectId(),
infoId:001,
userName:a,
designation:aa,
year:10
},
{
_id:ObjectId(),
infoId:001,
userName:y,
designation:yy,
year:3
},
{
_id:ObjectId(),
infoId:001,
userName:c,
designation:cc,
year:10
}
]}
}]
But in this say I want give some limit in such way that it need to retrieve upto that only.
If I am giving say limit as 4, then it need to return _id:003, _id:002, in _id:001 only two. Note: The limit whatever specifying should not for result after grouping.
So how can I query this in mongodb?