0

I started learning to use MongoDB along with Jupyter notebook and the python module Pymongo.

In one of my documents, I store data as following:

{
  "_id" : ObjectId("60b78821d5a53a1f12336580"),
  "num_arete" : "0",
  "date" : ISODate("2020-01-01T07:01:00Z"),
  "nb_vehicules" : "32"
}

I wanted, using aggregation, make the $sum of all the nb_vehicules within the document's collecions group by date.

Here's what I've tried:

passage_per_hour = collection_trafic.aggregate([{
    '$group': {
        '_id': '$date',
        'amount': { '$sum': {'$toInt' :'$nb_vehicules'}}
    }
}])

But when I run it inside the notenook, it doesn't perfom the group-by and display me $date as None (see below).

enter image description here

Do someone has any idea why ?

Eccsx
  • 185
  • 2
  • 13
  • What are you trying to achieve by grouping by date? It's extremely unlikely that two documents will have the EXACT same date, so there won't be any grouping with the aggregation you did. Does this answer on another question help? https://stackoverflow.com/a/16724320/7133623 – cdimitroulas Jun 02 '21 at 17:10
  • I know my query is more complex but I made this simple example because the problem seems to came from the date. – Eccsx Jun 02 '21 at 17:44
  • Collection names on the screenshot differ. The result says there are no single document with date field in "collection_trafic" – Alex Blex Jun 02 '21 at 21:59

1 Answers1

1

If you want to aggregate data by date then you need to extract only the date from the timestamp. As it will also contain time.

You can achieve that by using the below query for aggregation.

passage_per_hour = collection_trafic.aggregate([{
    '$group': {
        '_id': { '$dateToString': { 'format': "%Y-%m-%d", 'date': '$date' } },
        'amount': { '$sum': {'$toInt' :'$nb_vehicules'}}
    }
}])

EDIT:

To filter out null values of date

passage_per_hour = collection_trafic.aggregate([
{ 
   '$match':{
        'date':{"$exists":true}
   }
},
{
   '$group': {
       '_id': { '$dateToString': { 'format': "%Y-%m-%d", 'date': '$date' } },
       'amount': { '$sum': {'$toInt' :'$nb_vehicules'}}
    }
}])
Krunal Sonparate
  • 1,122
  • 10
  • 29