

Show HN: SQL to Mongo Query Translator - robertjmoore
http://www.querymongo.com/

======
polskibus
I don't have any experience with MongoDB, but the example put me off using it
completely. The MySQL query is concise and brief, the MongoDB equivalent is
bloated. Only after several seconds I was able to deduce that the MongoDB
query probably does something more than MySQL query. Can you please make the
examples more comparable? Or did I misunderstand the MongonDB and it actually
is so bloated by design? I believe it is not your goal to discourage people
from MongoDB , if so, better not do it unintentionally.

~~~
debacle
MongoDB's query language is just not very good.

~~~
hmexx
Used to make me want to tear my hair out... But I've moved to using linqpad
and writing the queries in linq now, and it's not too bad.

[http://stackoverflow.com/questions/7303333/are-adhoc-
queries...](http://stackoverflow.com/questions/7303333/are-adhoc-queries-
updates-starting-to-kill-your-productivity-with-mongodb?answertab=active#tab-
top)

------
rit
The default query already filled in is translating to the use of a Group
function, which is a very bad idea. While not deprecated per se, its use is
discouraged.

Group _does not function in Sharding mode at all_ , it also takes a lock on
the JavaScript interpreter making it non-parallelizable.

Map/Reduce is somewhat better in that it is shardable, and with V8 likely in
the next stable release, will have better parallelization prospects.

Ideally, you should be using the new Aggregation Framework to do this kind of
work: <http://docs.mongodb.org/manual/applications/aggregation/>

(EDIT) To clarify - Aggregation is ideal because its implementation is 100% in
C++, meaning there are no JavaScript interpreter locks necessary to run it, so
it is parallelizable. Additionally, one of the biggest overhead costs to
MapReduce and Group in MongoDB is the translation back and forth between BSON
(the native format MongoDB uses for data, or rather the C++ representations
thereof) and JavaScript types. Aggregation not utilizing JavaScript eliminates
this overhead and manipulates the database' internal types directly.

~~~
ukd1
You're correct, however note that 2.3/2.4 fixes the lock on the javascript
interpreter with the move to V8 as the default engine.

~~~
rit
I noted v8 in my comment; it won't be a panacea. It certainly doesn't fix the
encoding/decoding overhead of BSON<->JavaScript, but the JIT and
multithreading will help in other areas.

Group still will not become sharding-capable with v8, either.

------
redler
The example would be much clearer as an aggregation query. Roughly:

    
    
        db.demo.aggregate([
            { $match: { score: { $gt: 0 },
                        person: { $in: ["bob","jake"] }}},
            { $group: { _id: "person",
                        sumscore: { $sum: "$score" },
                        avgscore: { $avg: "$score" },
                        minscore: { $min: "$score" },
                        maxscore: { $max: "$score" },
                        count: { $sum: 1 }}}
        ]);

------
gry
I've also found this diagram incredibly helpful translating from a SQL to
map/reduce world.

[http://rickosborne.org/blog/2010/02/infographic-migrating-
fr...](http://rickosborne.org/blog/2010/02/infographic-migrating-from-sql-to-
mapreduce-with-mongodb/)

~~~
jaytaylor
Thanks @gry, and here is the direct link to the PDF:
<http://rickosborne.org/download/SQL-to-MongoDB.pdf>

------
rmrfrmrf
I didn't realise that Mongo was so...verbose.

------
sgt
Not particularly useful, I tried a simple join and I got this error message:

Failure parsing MySQL query: Unable to convert queries based on more than one
table

~~~
robertjmoore
Sorry about that! Built this in a hackathon and this first version doesn't
support joins yet.

~~~
sgt
If you built all this in a Hackathon then I'm impressed - well done!

------
e98cuenc
Very nice! I once thought about doing something like this, but I had some real
work to do. Thanks for this resource!

This is specially useful because of the verbosity and ugliness of the "JSON"
API (much more difficult to get right by hand than SQL) and because I found 0
working GUI tools to work with Mongo in a mac (they all crash at startup or
after ~5 seconds of usage in a modern mac).

~~~
w0utert
MongoHub (<http://mongohub.todayclose.com/>) works for me.

It's not very polished, but it gets the job done, and it makes it relatively
easy to edit stuff that's already in the database.

------
christkv
any particular reason you are not mapping to the aggregation framework which
is a lot faster ?

~~~
lucian1900
Perhaps less of SQL can be mapped to the aggregation framework than to
map/reduce.

------
rolfvandekrol
Nice work. Played around with it, and found that

SELECT * FROM demo WHERE score > 0 AND person LIKE '%bob%'

doesn't work that well. It will need to convert the %bob% thing to a regular
expression.

------
isabre
Awesome! I haven't played around with NoSQL databases and I always wondered
how you would query one. Definitely peaked my interest in the whole movement.

------
fox91
I find it pointless. If you want to write SQL queries just use a relational
database, if you want to use Mongo learn to use it.

~~~
hellopat
You could use it as a tool to learn Mongo query syntax assuming you have SQL
down.

------
nohaving2
HAVING clause is being ignored. i.e. try adding

    
    
      HAVING SUM(abc) > 1
    

to the example.

------
hayksaakian
Is there one that goes in the other direction?

------
aioprisan
are there any plans to open source this?

~~~
skram
+1 for this. Would be interested in seeing how this is done in code to further
my understanding of MongoDB. Would be interesting to create a reverse
translator as well.

------
camus
map/reduce anyone ? if you are using mongodb like a relational database , then
maybe you should just stick to relational databases...

~~~
cjfont
That's like saying if you're using a swiss army knife as a knife, maybe you
should just use a regular knife.

