CODEWITHSUNDEEP
mongodbnosqlmongodb-queryaggregation-framework
jschz
jschz

Reputation: 39

Group by hour and count MongoDB

I'm on a project working with data of a bike-sharing service. Each trip has the following info

> db.bikes.find({bike:9990}).pretty()
{
    "_id" : ObjectId("5bb59fd8e9fb374bf0cd5c1c"),
    "gender" : "M",
    "userAge" : 49,
    "bike" : 9990,
    "depStation" : 150,
    "depDate" : "01/08/2018",
    "depHour" : "0:00:13",
    "arrvStation" : 179,
    "arrvDate" : "01/08/2018",
    "arrvHour" : "0:23:38"
}

How do I group for each hour of the day and count the number of trips made in that specific hour? I'm trying with this query

db.bikes.aggregate(
  { 
     $group:{_id:{$hour: "$depHour"}, trips:{$sum: 1}}
  }
)

But it throws this error

    "ok" : 0,
    "errmsg" : "can't convert from BSON type string to Date",
    "code" : 16006,
    "codeName" : "Location16006"

Upvotes: 2

Views: 1124

Answers (2)

chridam
chridam

Reputation: 103475

The depDate and depHour fields are all string values that denote the day and the hour respectively so there is no need to use the date operators to convert the fields to date objects, all you need is to extract the hour part using $substrCP and then use them directly as expressions in your $group _id as:

db.bikes.aggregate([
    { '$group': {
        '_id': {
            'day': '$depDate',
            'hour': { '$substrCP': [ '$depHour', 0, 2 ] }
        }, 
        'trips': { '$sum': 1 }
    } }
])

Upvotes: 2

Ashh
Ashh

Reputation: 46491

You can try below aggregation with mognodb 4.0 

db.collection.aggregate([
  { "$group": {
    "_id": {
      "$dateToString": {
        "date": {
          "$toDate": { "$concat": ["$depDate", "T", "$depHour"] }
        },
        "format": "%Y-%m-%d-%H"
      }
    },
    "trips": { "$sum": 1 }
  }}
])

Upvotes: 1

Related Questions