CODEWITHSUNDEEP
mongodbruby-on-rails-4mongoidmongodb-queryaggregation-framework
Shekar
Shekar

Reputation: 11

How to count the elements in addToSet array in mongo query using rails 4

UserStayedTime.collection.aggregate({"$match" => { "rl_mac_id"=>{"$in" => rlids},"starttime"=> { "$gte" => start_date},"endtime" => {"$lte" => end_date}, "stayedtime" => {"$gte" => APPLICATION['defaults']['timeinterval'] } } }, {"$project"=>{"starttime"=>{"$add" => ["$starttime", offset.to_i]}, "visitor_detail_id"=>1}}, { "$group"=> {"_id"=> { "year"=> {"$year"=>"$starttime"},"month"=> {"$month"=>"$starttime"},"day"=> {"$dayOfMonth"=>"$starttime"}, "hour"=>{"$hour"=>"$starttime"}},  "visitors"=> { "$addToSet" => "$visitor_detail_id" }}})

when i write this query i got this result : 

[{"_id"=>{"year"=>2014, "month"=>9, "day"=>14, "hour"=>9}, "visitors"=>[BSON::ObjectId('54150f6969702d01d4e57b01'), BSON::ObjectId('54150cf669702d01d4197b01')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>14, "hour"=>8}, "visitors"=>[BSON::ObjectId('5415085b69702d01d47d7901'), BSON::ObjectId('541508b869702d01d4a47901'), BSON::ObjectId('5415052a69702d01d4547801'), BSON::ObjectId('541506f669702d01d4f47801'), BSON::ObjectId('5415025b69702d01d4597701'), BSON::ObjectId('5414ffe669702d01d47d7601')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>14, "hour"=>7}, "visitors"=>[BSON::ObjectId('5414fca769702d01d46a7501'), BSON::ObjectId('5414fbf769702d01d4307501'), BSON::ObjectId('5414f97b69702d01d4627401')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>14, "hour"=>6}, "visitors"=>[BSON::ObjectId('5414e5d369702d01d40f6e01'), BSON::ObjectId('5414e51d69702d01d4c76d01')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>11, "hour"=>22}, "visitors"=>[BSON::ObjectId('5411d53669702d01d4283300'), BSON::ObjectId('5411d53669702d01d4263300')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>14, "hour"=>4}, "visitors"=>[BSON::ObjectId('5414d07169702d01d4786701'), BSON::ObjectId('5414cdf669702d01d4b16601'), BSON::ObjectId('5414cd9a69702d01d48e6601'), BSON::ObjectId('5414cb7c69702d01d4e96501')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>14, "hour"=>1}, "visitors"=>[BSON::ObjectId('5414a40e69702d01d44a5a01')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>13, "hour"=>23}, "visitors"=>[BSON::ObjectId('54148d2169702d01d4765301')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>12, "hour"=>2}, "visitors"=>[BSON::ObjectId('54120af369702d01d4ee5600')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>13, "hour"=>4}, "visitors"=>[BSON::ObjectId('5413827a69702d01d4f6f800')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>10, "hour"=>17}, "visitors"=>[BSON::ObjectId('5410409169702d203c5f0100')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>14, "hour"=>5}, "visitors"=>[BSON::ObjectId('5414dc3b69702d01d4096b01'), BSON::ObjectId('5414dbe669702d01d4ee6a01'), BSON::ObjectId('5414d86969702d01d4e26901')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>13, "hour"=>0}, "visitors"=>[BSON::ObjectId('5413473b69702d01d459e500')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>12, "hour"=>1}, "visitors"=>[BSON::ObjectId('5412065969702d01d4405400'), BSON::ObjectId('5412011869702d01d41e5100'), BSON::ObjectId('5411fe3e69702d01d4774f00')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>13, "hour"=>2}, "visitors"=>[BSON::ObjectId('54135d1469702d01d478ec00'), BSON::ObjectId('541358ec69702d01d40ceb00')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>12, "hour"=>19}, "visitors"=>[BSON::ObjectId('541300e169702d01d43fd100')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>12, "hour"=>5}, "visitors"=>[BSON::ObjectId('54123def69702d01d4d57400')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>12, "hour"=>3}, "visitors"=>[BSON::ObjectId('5412228d69702d01d4aa6400'), BSON::ObjectId('54121b3569702d01d4636000')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>11, "hour"=>23}, "visitors"=>[BSON::ObjectId('5411e9ae69702d01d4fd4100')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>10, "hour"=>18}, "visitors"=>[BSON::ObjectId('541048f069702d203cae0100')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>11, "hour"=>5}, "visitors"=>[BSON::ObjectId('5410ead469702d203c530b00'), BSON::ObjectId('5410e96669702d203c360b00'), BSON::ObjectId('5410e74969702d203c0b0b00')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>11, "hour"=>21}, "visitors"=>[BSON::ObjectId('5411c43a69702d01d4562700')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>11, "hour"=>19}, "visitors"=>[BSON::ObjectId('5411b28a69702d01d4451b00')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>13, "hour"=>1}, "visitors"=>[BSON::ObjectId('541354b169702d01d4a8e900')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>11, "hour"=>18}, "visitors"=>[BSON::ObjectId('5411a31869702d01d4f41000')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>10, "hour"=>23}, "visitors"=>[BSON::ObjectId('5410991569702d203ce90400'), BSON::ObjectId('541091ac69702d203c6b0400')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>12, "hour"=>23}, "visitors"=>[BSON::ObjectId('54133ba769702d01d475e100'), BSON::ObjectId('541335fe69702d01d4a2df00')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>11, "hour"=>2}, "visitors"=>[BSON::ObjectId('5410c0db69702d203c3a0800'), BSON::ObjectId('5410bcf169702d203cf80700'), BSON::ObjectId('5410b89c69702d203c810700')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>13, "hour"=>3}, "visitors"=>[BSON::ObjectId('5413717969702d01d435f300')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>11, "hour"=>0}, "visitors"=>[BSON::ObjectId('5410a38d69702d203cb90500')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>10, "hour"=>22}, "visitors"=>[BSON::ObjectId('5410844b69702d203c890300')]}, {"_id"=>{"year"=>2014, "month"=>9, "day"=>10, "hour"=>14}, "visitors"=>[BSON::ObjectId('5410176a69702d0c45a72e00'), BSON::ObjectId('5410176a69702d0c45a52e00'), BSON::ObjectId('5410176a69702d0c45a32e00'), BSON::ObjectId('54101a8f69702d203c060000'), BSON::ObjectId('5410176a69702d0c45a12e00')]}]

Form this result i need to count the visitors based on(group_by) hour only. I am not able to get like this result

 [{"_id"=>{"hour"=>22, "count"=>3 }},{"_id"=>{"hour"=>2, "count"=>5 }},....]

Upvotes: 0

Views: 1526

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151132

The $addToSet operator really just is another way of getting a "distinct" grouping and then placing that result in an array or "set". So for your purposes, this is basically the same and more efficient to just "group" on the "visitor_id" and then count the distinct occurrences:

UserStayedTime.collection.aggregate(
    { "$match" => { 
        "rl_mac_id" => { "$in" => rlids },
        "starttime"=> { "$gte" => start_date},
        "endtime" => {"$lte" => end_date}, 
        "stayedtime" => {"$gte" => APPLICATION['defaults']['timeinterval'] } 
    }}, 
    { "$project" =>{ 
        "starttime" => { "$add" => ["$starttime", offset.to_i] }, 
        "visitor_detail_id"=>1
    }}, 
    { "$group" => { 
        "_id"=> { 
            "hour" => { "$hour"=>"$starttime"},
            "visitor" => "$visitor_detail_id" 
        }
    }},
    { "$group" => {
        "_id" => "$_id.hour",
        "count" => { "$sum" => 1 }
    }}
)

This is simply a "double $group" as you get the distinct members and then count them per hour. Presuming of course that your date range does not overlap a day or you otherwise don't mind where it does. Otherwise just add the other date parts to the grouping as before.

You could of course $unwind the "set" once you produced it, and then $group again for the count, but that really is not necessary or as efficient as just grouping by the visitor key.

Upvotes: 1

Related Questions