Reputation: 567
How can I do a query in Mongodb to find the count of entry and exit group by user for a location. the above collection have many locations for which user can enter or exit.
{ "ActivityList" : [
{ "type" : "entry",
"timestamp" : Date( 1344473257320 ),
"user" : { "$ref" : "userProfile",
"$id" : ObjectId( "4fdeaf6fde26fd298262bb81" ) } },
{ "type" : "exit",
"timestamp" : Date( 1348792321111 ),
"user" : { "$ref" : "userProfile",
"$id" : ObjectId( "4fdeaf6fde26fd298262bb81" ) } },
{ "type" : "entry",
"timestamp" : Date( 1348881701129 ),
"user" : { "$ref" : "userProfile",
"$id" : ObjectId( "4fdeaf6fde26fd298262bb81" ) } },
{ "type" : "exit",
"timestamp" : Date( 1348942808700 ),
"user" : { "$ref" : "userProfile",
"$id" : ObjectId( "4fdeaf6fde26fd298262bb81" ) } },
{ "type" : "entry",
"timestamp" : Date( 1348957400052 ),
"user" : { "$ref" : "userProfile",
"$id" : ObjectId( "4fdeaf6fde26fd298262bb81" ) } },
{ "type" : "exit",
"timestamp" : Date( 1349024290729 ),
"user" : { "$ref" : "userProfile",
"$id" : ObjectId( "4fdeaf6fde26fd298262bb81" ) } } ],
"Loc" : { "$ref" : "location",
"$id" : ObjectId( "501cb6d4e7e9a8f958f903c5" ) },
"_id" : ObjectId( "502308a9e7e91ab176f6708e" ) }
I am trying something like this but not successful ,see here
select a,b,sum(c) csum from coll where active=1 group by a,b
db.coll.group(
{key: { a:true, b:true },
cond: { active:1 },
reduce: function(obj,prev) { prev.csum += obj.c; },
initial: { csum: 0 }
});
Upvotes: 4
Views: 13667
Reputation: 65443
The document structure you are using (an array of items) will require some manipulation in the $group()
function that would be easier done with MapReduce or the new Aggregation Framework in MongoDB 2.2.
Here's an example using the Aggregation Framework:
db.activity.aggregate(
// Find matching documents first (can take advantage of index)
{ $match : {
Loc: DBRef("location", ObjectId("501cb6d4e7e9a8f958f903c5"))
}},
// Unwind the ActivityList array as a document stream
{ $unwind : "$ActivityList" },
// Group the stream by activity types for a user
{ $group : {
_id : "$ActivityList.user",
activity: { $push: "$ActivityList.type" }
}},
// Unwind the activity types so they can be counted
{ $unwind : "$activity" },
// Final grouping: count of action (entry or exit) for each user
{ $group : {
_id : { user: "$_id", action: "$activity" },
count: { $sum: 1 }
}}
)
Which produces a result similar to:
{
"result" : [
{
"_id" : {
"user" : DBRef("userProfile", ObjectId("4fdeaf6fde26fd298262bb81")),
"action" : "exit"
},
"count" : 6
},
{
"_id" : {
"user" : DBRef("userProfile", ObjectId("4fdeaf6fde26fd298262bb81")),
"action" : "entry"
},
"count" : 3
},
{
"_id" : {
"user" : DBRef("userProfile", ObjectId("4fdeaf6fde26fd298262bb82")),
"action" : "entry"
},
"count" : 2
},
{
"_id" : {
"user" : DBRef("userProfile", ObjectId("4fdeaf6fde26fd298262bb85")),
"action" : "entry"
},
"count" : 1
}
],
"ok" : 1
}
Upvotes: 5