Aggregate query on time in MongoDB
NickName:Syl Ask DateTime:2015-02-02T02:26:34

Aggregate query on time in MongoDB

I'm using MongoDB Aggregate pipeline and for most of the things I want to do, it's perfect so far.

However, I'd like to aggregate on time and group by individual days, hours or minutes. This is my query so far (in the case of minute granularity:

db.track.aggregate(
  [
    {$match: {event: /pricing/i}},
    {$unwind:"$userId"},
    {
      $group: {
        _id: {min: {$minute: "$timestamp"}},
        count: {$sum: 1}
      }
    }
  ]
)

The obvious problem here is that it will group minutes of different hours together. like so:

{ "_id" : { "min" : 18 }, "count" : 8 }
{ "_id" : { "min" : 33 }, "count" : 18 }
{ "_id" : { "min" : 10 }, "count" : 6 }
{ "_id" : { "min" : 8 }, "count" : 2 }
{ "_id" : { "min" : 43 }, "count" : 2 }
{ "_id" : { "min" : 35 }, "count" : 6 }
{ "_id" : { "min" : 46 }, "count" : 2 }
{ "_id" : { "min" : 12 }, "count" : 4 }
{ "_id" : { "min" : 31 }, "count" : 4 }
{ "_id" : { "min" : 4 }, "count" : 14 }

I want to use the result of that query in a graph. Ideally, I would like to get back something like that:

{ "_id" : { "time" : "14:04" }, "count" : 14 }

My document look like this

{
    _id: ObjectId("54cd7b8f7e4515a41898faac"),
    userId: [
        "xp1ungmsrh3hbhjk7c2go45xxvh0uiaa9rel5",
        "a3c10b3c-3825-4b32-9a57-0e75b508d5bb"
    ],
    type: "track",
    timestamp: ISODate("2015-02-01T01:04:13.632Z"),
    event: "View Pricing Page"
}

I'm sure I'm missing something obvious here but the doc doesn't give me anything else to go on.

Anyone can point me in the right direction?

Copyright Notice:Content Author:「Syl」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/28266203/aggregate-query-on-time-in-mongodb

Answers
Disposer 2015-02-01T19:18:14

Uae this query:\n\ndb.track.aggregate(\n [\n { $match: {event: /pricing/i} },\n { $unwind: \"$userId\" },\n {\n $group: \n {\n _id: \n {\n hour : { $hour : \"$timestamp\" },\n min: { $minute : \"$timestamp\" }\n },\n count: {$sum : 1}\n }\n },\n { $project : { _id : 0, count: 1, time : { $concat :[ {$substr:['$_id.hour', 0, 4]}, ':', {$substr:['$_id.min', 0, 4]}] }} },\n ]\n)\n\n\nSample output:\n\n{\n \"result\" : [ \n {\n \"count\" : 2,\n \"time\" : \"1:4\"\n }\n ],\n \"ok\" : 1\n}\n\n\nIf you change the last $project to below, the output will be exactly as you mentioned\n\n{ $project : { _id : 0, count: 1, '_id.time' : { $concat :[ {$substr:['$_id.hour', 0, 4]}, ':', {$substr:['$_id.min', 0, 4]}] }} },\n\n\nOutput:\n\n{\n \"result\" : [ \n {\n \"_id\" : {\n \"time\" : \"1:4\"\n },\n \"count\" : 2\n }\n ],\n \"ok\" : 1\n}\n",


More about “Aggregate query on time in MongoDB” related questions

Aggregate query on time in MongoDB

I'm using MongoDB Aggregate pipeline and for most of the things I want to do, it's perfect so far. However, I'd like to aggregate on time and group by individual days, hours or minutes. This is my...

Show Detail

mongoDb $in with aggregate query

How do I write an $in query along with aggregate in mongoDB? I want to write an equivalent mongoDB query for the SQL below SELECT name,count(something) from collection1 where name in (<<list...

Show Detail

MongoDB aggregate query

In MongoDB I have a collection: Statistics { UserID: int //User id Url: string //Url Clicks: [DateTime] //A time array } When a user clicks an url add a dat...

Show Detail

Count of the result of a MongoDB aggregate query

After I write a MongoDB aggregate command, I would like to quickly look at the number of results it returned. Say, my query is something like this: (probably more complex) db.zipcodes.aggregate(...

Show Detail

Aggregate Query in MongoDb with Node JS

I am new to Mongodb.i have two collections users and products. In product collection user id is mapped.product collection has productid,producttype,productname and userid as fields. i need to write

Show Detail

MongoDB Aggregate Query Group by Dates

I have the following collection in mongoDB { _id, startTime, duration } So the basic idea is that a camera looks out for people and once it detects a person it saves the startTime and once a person

Show Detail

MongoDB query for aggregate function retuning empty result

Following is the query i had written to get the count based on event name. $data= DB::collection(Events::$collection)->raw(function($collection) { return $collection->aggregate([ ['$...

Show Detail

MongoDB aggregate query to SpringDataMongoDB

I have below MongoDB aggregate query and would like to have it's equivalent SpringData Mongodb query. MongoDB Aggregate Query : db.response.aggregate( // Pipeline [ //

Show Detail

Mongodb aggregate query with condition

I have to perform aggregate on mongodb in python and unable to do so. Below is the structure of mongodb document extracted: {'Category': 'Male', 'details' :[{'name':'Sachin','height'

Show Detail

MongoDB Aggregate Query in Scala

I'm attempting to convert my MongoDB aggregate query: db.identification.aggregate([ { $match: { userId: "b0e644d2-5a0c-4048-abea-6ae17c337e57" } }, { $

Show Detail