[ad_1]
\n<\/p>\n
Though it should have been made more clear in your question, your output sample from source suggests that you are looking for:<\/p>\n
This is all possible in a single aggregation statement, and it just takes some careful management of the distinct lists and then some manipulation to map results for each hour in a 24 hour period.<\/p>\n
The best approach here is aided by operators introduced in MongoDB 3.2:<\/p>\n
db.collection.aggregate([\n \/\/ First group by hour within \"uid\" and keep distinct \"to\" and \"from\"\n { \"$group\": {\n \"_id\": {\n \"uid\": \"$uid\",\n \"time\": { \"$hour\": \"$timestamp\" }\n },\n \"from\": { \"$addToSet\": \"$from\" },\n \"to\": { \"$addToSet\": \"$to\" },\n \"count\": { \"$sum\": 1 }\n }},\n\n \/\/ Roll-up to \"uid\" and keep each hour in an array\n { \"$group\": {\n \"_id\": \"$_id.uid\",\n \"total\": { \"$sum\": \"$count\" },\n \"from\": { \"$addToSet\": \"$from\" },\n \"to\": { \"$addToSet\": \"$to\" },\n \"temp_hours\": { \n \"$push\": {\n \"index\": \"$_id.time\",\n \"count\": \"$count\"\n }\n }\n }},\n\n \/\/ Getting distinct \"to\" and \"from\" requires a double unwind of arrays\n { \"$unwind\": \"$to\" },\n { \"$unwind\": \"$to\" },\n { \"$unwind\": \"$from\" },\n { \"$unwind\": \"$from\" },\n\n \/\/ And then adding back to sets for distinct\n { \"$group\": {\n \"_id\": \"$_id\",\n \"total\": { \"$first\": \"$total\" },\n \"from\": { \"$addToSet\": \"$from\" },\n \"to\": { \"$addToSet\": \"$to\" },\n \"temp_hours\": { \"$first\": \"$temp_hours\" }\n }},\n\n \/\/ Map out for each hour and count size of distinct lists\n { \"$project\": {\n \"count\": \"$total\",\n \"from_count\": { \"$size\": \"$from\" },\n \"to_count\": { \"$size\": \"$to\" },\n \"hours\": {\n \"$map\": {\n \"input\": [\n 00,01,02,03,04,05,06,07,08,09,10,11,\n 12,13,14,15,16,17,18,19,20,21,22,23\n ],\n \"as\": \"el\",\n \"in\": {\n \"$ifNull\": [\n { \"$arrayElemAt\": [\n { \"$map\": {\n \"input\": { \"$filter\": {\n \"input\": \"$temp_hours\",\n \"as\": \"tmp\",\n \"cond\": {\n \"$eq\": [ \"$$el\", \"$$tmp.index\" ]\n }\n }},\n \"as\": \"out\",\n \"in\": \"$$out.count\"\n }},\n 0\n ]},\n 0\n ]\n }\n }\n }\n }},\n\n \/\/ Optionally sort in \"uid\" order\n { \"$sort\": { \"_id\": 1 } }\n ])\n<\/code><\/pre>\nPrior MongoDB 3.2 you need to get a bit more involved to map the array content for all hours in the day:<\/p>\n
db.collection.aggregate([\n\n \/\/ First group by hour within \"uid\" and keep distinct \"to\" and \"from\"\n { \"$group\": {\n \"_id\": {\n \"uid\": \"$uid\",\n \"time\": { \"$hour\": \"$timestamp\" }\n },\n \"from\": { \"$addToSet\": \"$from\" },\n \"to\": { \"$addToSet\": \"$to\" },\n \"count\": { \"$sum\": 1 }\n }},\n\n \/\/ Roll-up to \"uid\" and keep each hour in an array\n { \"$group\": {\n \"_id\": \"$_id.uid\",\n \"total\": { \"$sum\": \"$count\" },\n \"from\": { \"$addToSet\": \"$from\" },\n \"to\": { \"$addToSet\": \"$to\" },\n \"temp_hours\": { \n \"$push\": {\n \"index\": \"$_id.time\",\n \"count\": \"$count\"\n }\n }\n }},\n\n \/\/ Getting distinct \"to\" and \"from\" requires a double unwind of arrays\n { \"$unwind\": \"$to\" },\n { \"$unwind\": \"$to\" },\n { \"$unwind\": \"$from\" },\n { \"$unwind\": \"$from\" },\n\n \/\/ And then adding back to sets for distinct, also adding the indexes array\n { \"$group\": {\n \"_id\": \"$_id\",\n \"total\": { \"$first\": \"$total\" },\n \"from\": { \"$addToSet\": \"$from\" },\n \"to\": { \"$addToSet\": \"$to\" },\n \"temp_hours\": { \"$first\": \"$temp_hours\" },\n \"indexes\": { \"$first\": { \"$literal\": [\n 00,01,02,03,04,05,06,07,08,09,10,11,\n 12,13,14,15,16,17,18,19,20,21,22,23\n ] } }\n }},\n\n \/\/ Denormalize both arrays\n { \"$unwind\": \"$temp_hours\" },\n { \"$unwind\": \"$indexes\" },\n\n \/\/ Marry up the index entries and keep either the value or 0\n \/\/ Note you are normalizing the double unwind to distinct index\n { \"$group\": {\n \"_id\": {\n \"_id\": \"$_id\",\n \"index\": \"$indexes\"\n },\n \"total\": { \"$first\": \"$total\" }, \n \"from\": { \"$first\": \"$from\" },\n \"to\": { \"$first\": \"$to\" },\n \"count\": {\n \"$max\": {\n \"$cond\": [\n { \"$eq\": [ \"$indexes\", \"$temp_hours.index\" ] },\n \"$temp_hours.count\",\n 0\n ]\n }\n }\n }},\n\n \/\/ Sort to keep index order - !!Important!! \n { \"$sort\": { \"_id\": 1 } },\n\n \/\/ Put the hours into the array and get sizes for other results\n { \"$group\": {\n \"_id\": \"$_id._id\",\n \"count\": { \"$first\": \"$total\" },\n \"from_count\": { \"$first\": { \"$size\": \"$from\" } },\n \"to_count\": { \"$first\": { \"$size\": \"$to\" } },\n \"hours\": { \"$push\": \"$count\" }\n }},\n\n \/\/ Optionally sort in \"uid\" order\n { \"$sort\": { \"_id\": 1 } }\n])\n<\/code><\/pre>\nTo break that down, both approaches here follow the same basic steps, with the only real difference occuring on the mapping of “hours” for the 24 hour period.<\/p>\n
In the first aggregation $group<\/code><\/a> stage, the objective is to get results per hour present in the data and for each “uid” value. The simple date aggregation operator of $hour<\/code><\/a> helps obtain this value as part of the grouping key.<\/p>\nThe $addToSet<\/code><\/a> operations are a sort of “mini-group” in themselves, and this is allowing to keep the “distinct sets” for each of the “to” and “from” values whilst essentially still grouping per hour.<\/p>\nThe next $group<\/code> is more “organizational”, as the recorded “counts” for each hour are kept in an array whilst rolling up all the data to just be grouped per “uid”. This basically gives you all the “data” you really need for the result, but of course the $addToSet<\/code> operations here are just adding “arrays within arrays” of the distinct sets determined per hour.<\/p>\nIn order to get these values as truly distinct lists per each “uid” and only, it is necessary to deconstruct each array using $unwind<\/code><\/a> and then finally group back as just the distinct “sets”. The same $addToSet<\/code> compacts this, and the $first<\/code><\/a> operations just take the “first” values of the other fields, which are already all the same for the target “per uid” data. We are happy with those, so just keep them as they are.<\/p>\nThe final stage(s) here are essentially “cosmetic” in nature and can equally be achieved in client side code. Since there is not data present for every single hour interval, it needs to be mapped into an array of values representing each hour. The two approaches here vary on the capabilties of the available operators between versions.<\/p>\n
In the MongoDB 3.2 release, there are $filter<\/code><\/a> and $arrayElemAt<\/code><\/a> operators that effectively allow you to create the logic to “transpose” an input source of all possible index positions ( 24 hours ) into the values that are already determined for the counts fom those hours in the available data. This is basicalyl a “direct lookup” of values already recorded for each available hour to see if it exists, where it does the count is transposed into the full array. Where it is not present, a default value of 0<\/code> is used in place.<\/p>\nWithout those operators, doing this “match up” essentially means de-normalizing both arrays ( the recorded data and the full 24 positions ) in order to compare and transpose. This is what is happening in the second approach with a simple comparison of the “index” values to see if there was a result for that hour. The $max<\/code><\/a> operator here is mainly used because of the two $unwind<\/code> statements, where each recorded value from the source data is going to be reproduced for every possible index position. This “compacts” down to just the values that are wanted per “index hour”.<\/p>\nIn that latter approach it then becomes important to $sort<\/code><\/a> on the grouping _id<\/code> value. This is because it contains the “index” position, and that is going to be needed when moving this content back into an array you expect to be orderred. Which is of course the final $group<\/code> stage here where the ordered positions are put into an array with $push<\/code>.<\/p>\n