proposed new syntax for mongodb

I develop in node.js on mongodb database.

In several occasions I have found myself spending time to plan, learn and implement groupby queries and calculations and on little more complex then simple filter queries.

like group by queries of documents with existing and non existing parameters of sub-objects in an array

like 20-60 minutes for something i could do in 1 minute with mysql and some caching.

I think to implement a query preprocessor function for mongodb like this:

do you have any comments? or your version for syntax to do simple select from mongodb ?

output:

simple result is an array

groupby result is object and subobject and array ... as required in group by

or groupby as array as the result in mysql

there is some progression between versions

input:

//v1: //too hard to read
db.select({cols:['foo','moo'],from:'bar',where: ['(','a','=','15','and','b','=','15',')','or','z','=','10']}}) 
//v2: 
db.select({cols:['foo','moo'],from:'bar',where:{$or:{$and:{a:1,b:2},c: 3}}) 
//v3: 
db.select({cols:['foo','moo'],from:'bar',where:{a:1}})
//v4: //collection.select + callback: 
mycollection.select({where:{a:1}},callback); 
//v5: 
db.select({cols:['foo','moo'],from:'bar',where:function{ return (a==1 && b==2) || c==3; } ); 
//v6: 
db.select({cols:{'bar': ['foo','moo']},from:'bar',where:function{ return (a==1 && b==2) || c==3; } ); 
//v7: 
db.select({cols:{'bar':[{'4foo':function(){return bar.foo*5}},'moo']},from:'bar',where:function{ return (a==1 && b==2) || c==3; } ); 
//v8: 
//where:function might not work because of joins 
collection.select({cols:['foo','moo'],from: ['bar','baz'],where:function{ return (bar.a==1 && b==2) || baz.c==3; }, groupby:['foo'], orderby:[{moo:'asc'}, foo:'desc'}] ); 
//v9: 
db.select({cols:['foo','moo'],from:['bar','baz'],where:'(bar.a==1 && b==2) || baz.c==3', groupby:['foo'], orderby:[{moo:'asc'}, {foo:'desc'}] ); 
//v10: 
db.select({cols:['foo','moo'],from:['bar','baz'],where:['(bar.a==? && b==?) || baz.c==?',1,2,3], groupby:['foo'], orderby:[{moo:'asc'}, {foo:'desc'}] ); 
//v11: 
db.select({cols:['foo','moo'],from:['bar','baz'],where:['(bar.a==',1,' && b==',2,') || baz.c==',3], groupby:['foo'], orderby:[{moo:'asc'}, {foo:'desc'}] ); 
//v12: 
db.select({cols:['foo','moo'],from:['bar','baz'],where:['min(a,b)==', 2,' || baz.c==',3], groupby:['foo'], orderby:[{moo:'asc'}, {foo:'desc'}] ); 
//v13: 
db.select({cols:['foo','moo'],from: {'bar',db.mycollection,'baz':db.mycollection2},where:['min(a,b)==',2,' || baz.c==',3], groupby:['foo'], orderby:[{moo:'asc'}, foo:'desc'}] ); 
//v14: 
db.select({cols:['foo','moo'],from: {'bar',db.mycollection,'baz':db.mycollection2},where:['min(a,b)==',2,' || baz.c==',3], groupby:['foo'], orderby:[{moo:'asc'}, {foo:'desc'}],limit:{from:150,limit:10} ); 
//v15: 
db.select({cols:['foo','moo'],from: {'bar',db.mycollection,'baz':db.mycollection2},where:['min(a,b)==',2,' || baz.c in ',[1,2,3] ], groupby:['foo'], orderby:[{moo:'asc'}, {foo:'desc'}],limit:{from:150,limit:10} ); 
//v16: 
db.select({cols:['foo','moo'],from: {'bar',db.mycollection,'baz':db.mycollection2},where:['min(a,b)==',2,' && c $elemMatch ',{foo:baz},' || baz.d in ',[1,2,3] ], groupby:['foo'], orderby:[{moo:'asc'}, {foo:'desc'}],limit:{from:150,limit:10} ); 
//v17
query("select count(invitations_statuses[].status_value) from invitation_statues where ObjectId(.1..) in invitations_participating group by invitations_statuses[].status_value");
//v18
select('foo','bar','baz.moo').from(db.mycollection,db.myotherccollection).where('bar=',10)
//v19
db.select('foo','avg(bar) as avgbar','baz.moo').from(db.mycollection,'.foo.bar',db.myothercollection).where('bar=',10,'and foo=2 and obj=',{a:1234}).groupby('bar').toArray();
v20
select('foo','bar','baz.moo').from(db.mycollection,db.myotherccollection).where('bar=',10)
.union().select('foo','bar','baz.moo').from(db.mycollection,db.myotherccollection).where('bar=',10).toarray();
v21
query("select name,id from invitation_statues[id=10].bar[id=5].baz[name like /foo/]"); // its not sql but kind of convenient
v22
query("select baz from invitation_statues[id=10].bar[id=5] where baz[name = /foo/]"); 
v23
query("select baz from invitation_statues[id=10].bar[id=5] where baz[name = /foo/]"); 

Mongodb should be based on promises and the syntax should be native and the variable that holds the collections should be called db so it will be db.collection ...

https://github.com/lm1/node-fibers-promise

feel free to modify , discuss and add

http://rickosborne.org/blog/index.php/2010/02/19/yes-virginia-thats-automated-sql-to-mongodb-mapreduce/

http://code.google.com/p/qizmt/wiki/MySpaceQizmtSQLQuickStartGuideQSQLTranslation