Convert an SQL into pymongo query

Question

Document prototype:

{
    "d": "D", 
    "g": {
        "c": "C", 
        "a": "A", 
        "b": "B"
    }, 
    "e": "E", 
    "f": "F"
}

What would be the equivalent of:

SELECT a, b, c, d from Table WHERE d='D' AND e='E' GROUP BY a

in mongodb using pymongo?

The following query returns objects:

db.<collection>.find({'d': 'D'}, {'g.c': 1, 'g.a': 1, 'g.b': 1, 'd': 1, '_id': 0})

But, the following doesn't:

db.<collection>.aggregate([{$match:{"d":"D", "e":"E"}},
                           {$group:{_id:"$g.a"}}])

It returns an empty list, not even a query(cursor) object.

Also, how could I include $project into it so that I may restrict the output to the fields of a, b ,c d only?

Note I 've created the collection so as to filter e='E'.

Normally we don't answer these types of questions that demonstrate no effort on your part to fix the problem, however, this time appears some one has answered — Sammaye
– Sammaye, Commented Aug 22, 2013 at 6:59
Sorry if it looks that way. But effort has been put into it. One must not be so quick and sure at judging things. At least when they can't bother to approach the solution themselves. Sorry again if too rude. @Sammaye — Anshu Kumar
– Anshu Kumar, Commented Aug 22, 2013 at 7:18
Maybe if you showed effort I would be "bothered", also your edit, it works perfectly fine: > db.z.insert({d:'D',g:{c:'C',a:'A',b:'B'},e:'E',f:'F'}) > db.z.aggregate([{$match:{d:'D',e:'E'}},{$group:{_id:'$g.a'}}]) { "result" : [ { "_id" : "A" } ], "ok" : 1 } Also that is not valid pymongo syntax — Sammaye
– Sammaye, Commented Aug 22, 2013 at 7:31

roman · Accepted Answer · 2018-07-02 20:45:16Z

2

Actually your query is not a valid SQL.

You have to quote strings

SELECT a, b, c, d
from Table
WHERE d='D' AND e='E'
GROUP BY a

And still this query will work only in MySQL. For ANSI SQL (and most implementations) you should specify aggregates for your columns, like

SELECT a, min(b) as b, max(c) as c
from Table
WHERE d='D' AND e='E'
GROUP BY a

then your mongodb query would be like

db.<your collection>.aggregate([
   {$match:{"d":"D", "e":"E"}},
   {$group:{_id:"$g.a", b: { $min: "$g.b"}, c: {$max:"$g.c"}}}
])

if you want an array of a, b, c, d values, this should work:

db.<your collection>.aggregate([
   {$match:{"d": "D", "e": "E"}},
   {
       $group: {
            _id: "$g.a",
            data: {$push: {"a": "$g.a", "b": "$g.b", "c": "$g.c", "d": "$d"}}
       }
   }
])

Just tested this code - it works, here's python code:

>>> cl = MongoClient()
>>> coll = cl["local"]["test3"]
>>> res = coll.aggregate([{"$match":{"d": "D", "e": "E"}},{"$group":{"_id":"$g.a", "data": {"$push":{"a":"$g.a", "b":"$g.b", "c":"$g.c", "d":"$d"}}}}])
>>> res["result"]
[{'_id': 'A', 'data': [{'a': 'A', 'c': 'C', 'b': 'B', 'd': 'D'}, {'a': 'A', 'c': 'K', 'b': u'V', 'd': 'D'}]}]

edited Jul 2, 2018 at 20:45

answered Aug 22, 2013 at 6:13

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anshu Kumar Over a year ago

Thanks. Also, for the correction in SQL. However, quote was left behind intended, as an obvious thing. But that shouldn't had been. BTW, I don't have any requirement of AnsiSQL query here. I just have to get objects having a,b,c,d in it grouped by a and d should be D and e should be E. Could you render the query without the min and max functions?

Sammaye · Accepted Answer · 2013-08-22 07:44:21Z

0

As you can see the answer by @RomanPekar does actually work:

> db.z.insert({d:'D',g:{c:'C',a:'A',b:'B'},e:'E',f:'F'})
> db.z.aggregate([{$match:{d:'D',e:'E'}},{$group:{_id:'$g.a'}}])
{ "result" : [ { "_id" : "A" } ], "ok" : 1 }

The problem is, most likely, that you are trying to do this in python without changing the syntax. Instead you would run this in python:

db.z.aggregate([{"$match":{"d":"D","e":"E"}},{"$group":{"_id":"$g.a"}}])

For reference: http://api.mongodb.org/python/current/examples/aggregation.html

answered Aug 22, 2013 at 7:44

Sammaye

44k7 gold badges110 silver badges149 bronze badges

Collectives™ on Stack Overflow

Convert an SQL into pymongo query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related