How to make nested queries in MongoDb that works like nested Sql select queries

Question

I want to make an efficient query in MongoDb 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"]});

Now here is the select I want to do, but without hardcoding the ["u2","u3"] array:

db.user.find({_id:{$in:["u2","u3"]}}).forEach(printjson);

This works fine and returns the user objects for u2 and u3.

Now the question is how to get the array of userids in the $in operator extracted with a query such that the entire 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 with the official c# driver ?

Answer 1

The answer to such questions in MongoDB is often to denormalize your data. If you need just a list of the users in the group you could store the user Id and the user Name in the group document. In some ways you structure your database according to the result you want to see on screen rather than trying to put it in some normalized format.

Clearly that would only work if your user group list (with names) can fit in a single document, but your current approach has some limitations too concerning the maximum size of a group.

Another approach would be to store the groups that a user belongs to in an array on each 'User' document. Add an index on that array field and now you can find users by group. Given that a user is likely to belong to less groups than there are members in a group this may be the best approach here.

db.user.save({_id:"u1", name:"u1 name", groups:[{_id:"g1", name:"Group One"}, ...]});

Again you might store the group name with its _id so you can immediately display the list of groups a user belongs to with a single round trip. Of course, if you allow a group name to change you'll have to kick off a background task to go fix up all these copies of the name.

I would also use the built in MongoDB id generator rather than your own, it has many desirable properties.