CODEWITHSUNDEEP
mongodbpython-2.7pymongo
miku
miku

Reputation: 103

PyMongo query on Aggregation

I have written a query in mongodb using aggregation which is working fine but somehow it is not working properly in my python code with pymongo. Please advice how to rectify.

Mongo query:

 db.flights.aggregate([    
        { $match: { origin:"ATL", dest:"BOS",dayofweek: 3} },
        { $group: {          
            _id: {              
                origin:"$origin",             
                destination: "$dest"                   
            },
            Failure: { $sum: { $cond :  [{ $eq : ["$cancelled", 1]}, 1, 0]} },
            Success: { $sum: { $cond :  [{ $eq : ["$cancelled", 0]}, 1, 0]} },
            Total: { $sum: 1 }
        } }, 
        {$project:{Failure:1,Success:1, Total:1, FailPercent: { $divide: [ "$Failure", "$Total" ]}}},
        { $sort: { "_id.origin": 1, "_id.destination": 1 } } 
    ])

In python code:

client = MongoClient("localhost", 27017)
connect = client["database"]["collection"]

pipe2 = [ { '$match': { 'origin':"ATL", 'dest':"BOS",'dayofweek': 3} },
{ '$group': {          
    '_id': {              
        'origin':"$origin",             
        'destination': "$dest"                   
    },
    'Failure': { '$sum': { '$cond' :  [{ '$eq' : ["$cancelled", 1]}, 1, 0]} },
    'Success': { '$sum': { '$cond' :  [{ '$eq' : ["$cancelled", 0]}, 1, 0]} },
    'Total': { '$sum': 1 }
} },{'$project':{'Failure':1,'Success':1, 'Total':1, 'FailPercent': { '$divide': [ "$Failure", "$Total" ]}}},
 { '$sort': SON([("_id.origin", 1), ("_id.destination", 1 )]) } 
]
result = connect.aggregate(pipeline=pipe2)

the query result from pymongo is coming incorrect but in mongoDB it is correct

Upvotes: 2

Views: 12314

Answers (2)

Alexander
Alexander

Reputation: 188

A bit late, but for further whoever search for same error...
Looks like you aren't specifying proper collection.
In your first snippet you rely to db.flights, but in the second snippet you rely to ["database"]["collection"].
Try replacing the collection with actual collection's name.

Upvotes: 0

BMan
BMan

Reputation: 71

The "pipeline" variable seems unnecessary. Without seeing your error, and assuming your connection to the database is fine

This line:

result = connect.aggregate(pipeline=pipe2)

Should just be:

result = connect.aggregate(pipe2)

After duplicating your collection from the information given, this worked for me. Here's the full code (my connection looks a bit different than yours as well)

Collection:

{ '_id' : 1, 'origin' : 'ATL', 'dest' : 'BOS', 'dayofweek' : 3, 'cancelled' : 0 }

{ '_id' : 2, 'origin' : 'ATL', 'dest' : 'BOS', 'dayofweek' : 3, 'cancelled' : 0 }

{ '_id' : 3, 'origin' : 'ATL', 'dest' : 'BOS', 'dayofweek' : 3, 'cancelled' : 1 }

Code:

import pymongo
from bson.son import SON

connection_string = 'mongodb://localhost'
connection = pymongo.MongoClient(connection_string)
database = connection.myDatabase

pipe2 = [ { '$match' : { 'origin' : 'ATL',
                         'dest' : 'BOS',
                         'dayofweek' : 3
                       }
          },
          { '$group' : { '_id' : { 'origin' : '$origin',
                                   'destination' : '$dest'
                                 },
                         'Failure' : { '$sum' : { '$cond' : [{ '$eq' : ['$cancelled', 1]}, 1, 0 ]} },
                         'Success' : { '$sum' : { '$cond' : [{ '$eq' : ['$cancelled', 0]}, 1, 0 ]} },
                         'Total' : { '$sum' : 1 }
                        }
           },
           { '$project' : { 'Failure' : 1,
                            'Success' : 1,
                            'Total' : 1,
                            'FailPercent' : { '$divide' : [ '$Failure', '$Total' ] }
                          }
           },
           { '$sort' : SON([('_id.origin', 1), ('_id.destination', 1 )]) }
         ]

result = database.myCollection.aggregate(pipe2)
print(result)

output:

{u'ok' : 1.0, u'result' : [{u'Failure': 1, u'_id': { u'origin': u'ATL', u'destination': u'BOS'}, u'FailPercent': 0.333333333333, u'Success': 2, u'Total': 3}]}

Upvotes: 5

Related Questions