PyMongo not returning results on aggregation-CodePudding

I'm a total beginner in PyMongo. I'm trying to find activities that are registered multiple times. This code is returning an empty list. Could you please help me in finding the mistake:

rows = self.db.Activity.aggregate( [
    
    { '$group':{ 
        "_id": 
            {
                "user_id": "$user_id",  
                "transportation_mode": "$transportation_mode", 
                "start_date_time": "$start_date_time",
                "end_date_time": "$end_date_time"
            },           
        "count": {'$sum':1}
        }
    },
    
    {'$match': 
        { "count": { '$gt': 1 } }
    },
    
    {'$project': 
        {"_id":0,
        "user_id":"_id.user_id", 
        "transportation_mode":"_id.transportation_mode",  
        "start_date_time":"_id.start_date_time",
        "end_date_time":"_id.end_date_time",
        "count": 1

        }
    }
    ]
    )

5 rows from db:

{ "_id" : 0, "user_id" : "000", "start_date_time" : "2008-10-23 02:53:04", "end_date_time" : "2008-10-23 11:11:12" }

{ "_id" : 1, "user_id" : "000", "start_date_time" : "2008-10-24 02:09:59", "end_date_time" : "2008-10-24 02:47:06" }

{ "_id" : 2, "user_id" : "000", "start_date_time" : "2008-10-26 13:44:07", "end_date_time" : "2008-10-26 15:04:07" }

{ "_id" : 3, "user_id" : "000", "start_date_time" : "2008-10-27 11:54:49", "end_date_time" : "2008-10-27 12:05:54" }

{ "_id" : 4, "user_id" : "000", "start_date_time" : "2008-10-28 00:38:26", "end_date_time" : "2008-10-28 05:03:42" }

Thank you

CodePudding user response：

When you pass _id: 0 in the $project stage, it will not project the sub-objects even if they are projected in the follow up, since the rule is overwritten.

Try the below $project stage.

{
  '$project': {
    "user_id":"_id.user_id", 
    "transportation_mode":"_id.transportation_mode",  
    "start_date_time":"_id.start_date_time",
    "end_date_time":"_id.end_date_time",
    "count": 1
  }
}

rows = self.db.Activity.aggregate( [
  {
    '$group':{
      "_id": {
        "user_id": "$user_id",  
        "transportation_mode": "$transportation_mode", 
        "start_date_time": "$start_date_time",
        "end_date_time": "$end_date_time"
        },           
      "count": {'$sum':1}
      }
  },
  {
    '$match':{
      "count": { '$gt': 1 }
    }
  },
  {
    '$project': {
      "user_id":"_id.user_id", 
      "transportation_mode":"_id.transportation_mode",  
      "start_date_time":"_id.start_date_time",
      "end_date_time":"_id.end_date_time",
      "count": 1,
    }
  }
])

CodePudding user response：

Your group criteria is likely too narrow.

The $group stage will create a separate output document for each distinct value of the _id field. The pipeline in the question will only include two input documents in the same group if they have exactly the same value in all four of those fields.

In order for a count to be greater than 1, there must exist 2 documents with the same user, mode, and exactly the same start and end.

In the same data you show, there are no two documents that would be in the same group, so all of the output documents from the $group stage would have a count of 1, and therefore none of them satisfy the $match, and the return is an empty list.