I have the following collection which represents a swipe record when a member goes to the gym.
{
"_id" : ObjectId(""),
"content" : {
"Date_Key" : "",
"TRANSACTION_EVENT_KEY" : "",
"SITE_NAME" : "",
"Swipe_DateTime" : "",
"Gender" : "",
"Post_Out_Code" : "",
"Year_Of_Birth" : "",
"Time_Key" : "",
"MemberID_Hash" : "",
"Member_Key_Hash" : "",
"Swipes" : ""
},
"collection" : "observations"
}
I want to return the number of members for every number of gym swipes in a given month. For example:
{
{"nrOfGymSwipes": 0, "nrOfMembers": 10}, // 10 members who swiped 0 times
{"nrOfGymSwipes": 1, "nrOfMembers": 15}, // 15 members who swiped once
{"nrOfGymSwipes": 2, "nrOfMembers": 17},
...
}
I have tried the following:
collection
.aggregate(
[{$match: {"content.Swipe_DateTime": {$regex:"201602"}}},
{$group: {_id: "$content.MemberID_Hash", "nrOfGymSwipes":{$sum: 1}}},
{$sort: {"nrOfGymSwipes": 1}}],
which returns for each member the number of swipes in the given month.
.........
{ _id: '111', nrOfGymSwipes: 16 },
{ _id: '112', nrOfGymSwipes: 16 },
{ _id: '113', nrOfGymSwipes: 17 },
...............
Now I was thinking of doing a group by the number of gym swipes and count the ids, have tried this but it doesn't return what i expected
collection
.aggregate(
[{$match: {"content.Swipe_DateTime": {$regex:"201602"}}},
{$group: {_id: "$content.MemberID_Hash", "nrOfGymSwipes":{$sum: 1}}},
{$group: {_id: "nrOfGymSwipes", "nrOfMembers":{$sum: 1}}}, <---added this
{$sort: {"nrOfGymSwipes": 1}}],
Any idea how i can solve this? Also, is there a way to change the way i get the json output? for example instead of showing _id: "32131" part, output nrOfMembers: "312321"
You were almost there with your final group, you only needed to prefix your _id key with $ to indicate the number of swipes field. The $sort pipeline is where another problem is because the field you are trying to sort on does not exist. The aggregate pipeline works on the premise that results from a stage in the pipeline are passed on to the next as modified documents (with their own structure depending on the aggregate operation) and the last group pipeline only produces two fields, "_id" and "nrOfMembers".
You can use the $project pipeline step in order for the $sort stage to work since it creates the "nrOfGymSwipes" field for you by replacing the previous _id key and you can then get the final output in the desired structure. So you final aggregate operation should be:
collection.aggregate([
{ "$match": { "content.Swipe_DateTime": { "$regex":"201602" } } },
{ "$group": { "_id": "$content.MemberID_Hash", "nrOfGymSwipes": { "$sum": 1 } } },
{ "$group": { "_id": "$nrOfGymSwipes", "nrOfMembers": { "$sum": 1 } } },
{ "$project": { "_id": 0, "nrOfGymSwipes": "$_id", "nrOfMembers": 1 } },
{ "$sort": { "nrOfGymSwipes": 1 } }
], function (err, result) { ... });