PyMongo query on Aggregation

Question

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

what is the result you are getting back? – Asya Kamsky Nov 24 '14 at 09:35 — Asya Kamsky, Nov 24 '14 at 09:35

score 5 · Answer 1 · edited Jun 20 '20 at 09:12

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}]}

Hi BMan..thnx for your reply..so i understood this clearly, acc to you the unnecessary parameter is causing the error? i will try n share the result. And the connection to DB is fine and I checked tht as first thing. :) — miku, Nov 26 '14 at 09:23
@miku - My first impression was that the unnecessary parameter might be causing your error. However I gave the full code I used to get a successful output in case it wasn't. Please post the invalid output you are getting or the error if you are still having trouble. — BMan, Dec 01 '14 at 15:50

PyMongo query on Aggregation

1 Answers1