Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Aggregate query in mongo works, does not in Pymongo

I encountered a problem. I try to query this document to obtain the sum the amount and group by the LOC identifier that is outside the "COL" array.

{
"_id" : ObjectId("57506d74c469888f0d631be6"),
"LOC" : "User001",
"COL" : [ 
    {
        "date" : "25/03/2016",
        "number" : "Folio009",
        "amount" : 100
    }, 
    {
        "date" : "25/04/2016",
        "number" : "Folio010",
        "amount" : 100
    }

] }

This command works in mongo but I cannot make it work in Python with the Pymongo package:

Mongo query (working)

db.perfiles.aggregate({"$unwind": "$COL"},
{ "$group": { _id: "$LOC", "sum" : {"$sum" : "$COL.amount" }}})

Pymongo (not working)

from pymongo import MongoClient

client = MongoClient()

db = client['temporal']

docs = db.perfiles


pipeline = [{"$unwind": "$COL"},
     {"$group": {"_id": "$LOC", "count": {"$sum": "$COL.amount"}}}
          ]

list(db.docs.aggregate(pipeline))

Any suggestion to query this same query but in Pymongo? Thanks!

like image 300
Alfredo Avatar asked Jun 30 '16 21:06

Alfredo


2 Answers

I assume you have a valid connection to MongoDB in Python.
The following code snippet will return a MongoDB cursor in result.

pipeline = [
    {"$unwind": "$COL"},
    {"$group": {"_id": "$LOC", "sum": {"$sum": "$COL.amount"}}}
]

cursor = collection.aggregate(pipeline)

Now you can convert cursor to list

result = list(cursor)

and if you print result's value, you'll get exactly the same result as in your Shell query.

[{u'sum': 200.0, u'_id': u'User001'}]

Update:

I see that you are calling the aggregate function in python code as db.docs.aggregate(pipeline). You need to call it as docs.aggregate... without db. See example above.

like image 198
Saleem Avatar answered Nov 11 '22 06:11

Saleem


MongoDB Enterprise > db.test.aggregate([{$match:{name:'prasad'}},{$group : {_id : "$name", age : {$min : "$age"}}}]);
{ "_id" : "prasad", "age" : "20" }
MongoDB Enterprise > db.test.find()
{ "_id" : ObjectId("5890543bce1477899c6f05e8"), "name" : "prasad", "age" : "22" }
{ "_id" : ObjectId("5890543fce1477899c6f05e9"), "name" : "prasad", "age" : "21" }
{ "_id" : ObjectId("58905443ce1477899c6f05ea"), "name" : "prasad", "age" : "20" }
{ "_id" : ObjectId("5890544bce1477899c6f05eb"), "name" : "durga", "age" : "20" }
{ "_id" : ObjectId("58905451ce1477899c6f05ec"), "name" : "durga", "age" : "21" }
{ "_id" : ObjectId("58905454ce1477899c6f05ed"), "name" : "durga", "age" : "22" }
MongoDB Enterprise >    


############code


import pymongo
from pymongo import MongoClient
client=MongoClient("localhost:27017")
db=client.prasad      #####prasad is dbname, test is collection name
nameVar='prasad'
aggregation_string=[{"$match":{"name":nameVar}},{"$group" : {"_id" : "$name", "age" : {"$min" : "$age"}}}]
x=db.test.aggregate(aggregation_string)
print x
for r in x:
        min_age=r.items()[0]
        print(min_age[1])      #######output:      20
like image 22
y durga prasad Avatar answered Nov 11 '22 05:11

y durga prasad