How to make nested queries like nested sql selects, and how to do this in the official C# driver?

[sn]

// I want to make an efficient query to find all users who have their
userids listed in a usergroup.
// Ideally I want to make this as a single request to mongodb.
// What I want corresponds to nested selects in SQL.
// I have tried this in the mongo shell:

db.user.save({_id:"u1", Name:"u1 name"});
db.user.save({_id:"u2", Name:"u1 name"});
db.user.save({_id:"u3", Name:"u3 name"});
db.usergroup.save({_id:"g1", Users: ["u2","u3"]});

// This is the select I want to do, but without hardcoding the
["u2","u3"] array:
db.user.find({_id:{$in:["u2","u3"]}}).forEach(printjson);

// So the question is how to get the array of userids in the $in
operator extracted with a query such that the enite query can be made
with a single request.

// A "nested query" like this does not work:
//db.user.find({_id:{$in:db.usergroup.find({_id:"g1"},{_id:0,Users:
1})}}).forEach(printjson);
//Gives this error:
//Tue Mar 27 06:17:41 uncaught exception: error: { "$err" : "invalid
query", "code" : 12580 }
//failed to load: mongoNestedSelect.js

// 1) is this possible in mongodb and how ?
// 2) how to do this in the official c# driver ?

[H.J]

Hi,

May you can try this:

db.usergroup.find({"_id":"g1"}).forEach(function(doc){
db.user.find({_id:{$in:doc.Users}}).forEach(printjson);
});

2012/3/27 sn <st...@infotain.us>:

> --
> You received this message because you are subscribed to the Google Groups "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
>

--
尽人事，听天命！

[Sam Millman]

MongoDB cannot do true nested queries since even H.Js solution does not actually effect the outcome of the parent query but if you need additional information for your results that normally works.

In C# (I have not programmed C# in a while so this is theory) you will need to do your query and then foreach() the cursor to do a second query, just like it is done in JS.

2012/3/27 H.J <shiy...@gmail.com>

[sn]

Ok thanks.

On Mar 27, 9:19 am, Sam Millman <sam.mill...@gmail.com> wrote:
> MongoDB cannot do true nested queries since even H.Js solution does not
> actually effect the outcome of the parent query but if you need additional
> information for your results that normally works.
>
> In C# (I have not programmed C# in a while so this is theory) you will need
> to do your query and then foreach() the cursor to do a second query, just
> like it is done in JS.
>

> 2012/3/27 H.J <shiyi...@gmail.com>

>
>
>
>
>
>
>
> > Hi,
>
> > May you can try this:
>
> > db.usergroup.find({"_id":"g1"}).forEach(function(doc){
> > db.user.find({_id:{$in:doc.Users}}).forEach(printjson);
> > });
>

> > 2012/3/27 sn <s...@infotain.us>:

[Sam Millman]

np, though looking at it twice:

//db.user.find({_id:{$in:db.

usergroup.find({_id:"g1"},{_id:0,Users:
1})}}).forEach(printjson);

That might not have worked because of the return type, may have caused a malformed query. Most likely it would work in C# if you made a cleaning function to get out what you need since the return of the find() should have been (as you though) an arrray of _ids.

[Steve Francia]

MongoDB doesn't support subselects (yet). You can't perform a query
from within a query.

The most efficient approach today is to do two different queries. The
first query fetches the ids (and only the ids) you want to select in
the second query.
The second query then passes that array of ids into an $in query.
The result is that a single cursor is returned that you can iterate
over which is much more efficient than looping over the array and
performing a separate query each time.

var x = db.usergroup.find({_id:"g1"},{_id:0,Users: 1});
db.user.find({_id:{$in:x}}).forEach(printjson);


Ultimately looking at what you are trying to do it could make even
more sense to change your schema. This seems like a relational schema
and fails to take advantage of some of the rich data features MongoDB
provides.

As your users are likely not in a large number of groups, it probably
makes sense to embed the list of groups a user is in inside of the
user document.

Like

user = {
_id:"u1",
Name:"u1 name",
Groups: [ groupid1, groupid2, ... ]
}

Then this is a very simple query. I would create an index on the
Groups array by using

db.user.ensureIndex( { 'Groups' : 1});