20

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 ?

ssn
  • 2,337
  • 4
  • 21
  • 27

6 Answers6

11

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.

Ian Mercer
  • 38,490
  • 8
  • 97
  • 133
  • 2
    ok thanks. So "real nested queries" are not possible in MongoDB?. And yes I agree with your comment about ObjectIds, but I just used strings in this examples to keep the stuff simple. – ssn Mar 27 '12 at 06:58
  • 1
    Right, no nested queries, your data needs to be structured so you can get what you want with a single (or a minimum number) of requests to the database. – Ian Mercer Mar 27 '12 at 16:29
9

define function

function bbb(){
    var org_ids = new Array();
    var orgs = 
        db.orgTreeNode.find({ancestors:"ca5cd344-ba47-4601-a07b-ea2c684bfb4e"},{"_id":1});
    orgs.forEach(function(org){
        org_ids.push(org._id);
    })

    return db.user.find({"org":{$in:org_ids}}).skip(300).limit(10);
}

execute function

bbb()
pippo
  • 91
  • 1
  • 1
  • This should be the accepted answer. It answer the question for nested query in 2 db calls only instead of O(NxN) otherwise. Thank you @pippo – Pam Stums Oct 14 '21 at 09:28
3

If it can trigger to get you ans-- 

db.users.find({
    _id: {
        $in: db.logs.find({
            loggedbyuser: {
                $ne: ObjectId("569f9d093447ee781ca80b52")
            },
            logtype: "marketfetched",
            "logcreated": {
                $gt: new ISODate("2016-02-06T00:00:00.871Z")
            }
        }, {
            loggedbyuser: 1,
            _id: 0
        }).sort({
            'logcreated': -1
        }).map(function(like) {
            return like.loggedbyuser;
        })
    }
}).map(function(like) {
    return like.fullname;
});
David Maust
  • 8,080
  • 3
  • 32
  • 36
Markrings
  • 39
  • 1
  • 2
    The op is asking for a nested query similar to SQL which executes in only one step inside the DB. You are just fetching the results of the "nested" query, and the sending it all over the wire again. – Leandro Glossman Jun 15 '16 at 16:24
2

-sUReN

SQL Query: (group by & count of distinct)

select city,count(distinct(emailId)) from TransactionDetails group by city;

Equivalent mongo query would look like this:

db.TransactionDetails.aggregate([ 
{$group:{_id:{"CITY" : "$cityName"},uniqueCount: {$addToSet: "$emailId"}}},
{$project:{"CITY":1,uniqueCustomerCount:{$size:"$uniqueCount"}} } 
]);
Surendranath Reddy K
  • 641
  • 7
  • 5
1
db.usergroup.aggregate([
  { $match: { _id: "g1" } }, 
  { $unwind: "$Users" }, 
  { $lookup: 
    { from: "user", localField: "Users", foreignField: "_id", as: "user" } 
  } 
])

// result :
{ "_id" : "g1", "Users" : "u2", "user" : [ { "_id" : "u2", "Name" : "u1 name" } ] }
{ "_id" : "g1", "Users" : "u3", "user" : [ { "_id" : "u3", "Name" : "u3 name" } ] }
d9ngle
  • 1,303
  • 3
  • 13
  • 30
0

I was wondering the same thing about nested queries in mongodb. For my case, I putz a bit and got it to work and I think yours would too by using the "$or" and use toArray()

db.user.find({$or:db.usergroup.find({_id:"g1"},{_id:0,Users:1}).toArray()})

May not have that quite right but this is an example with my data that worked using a nexted query. Obviously a little silly as why feed ids back into another query in this case but wanted to see if it would work and it did:

db.notification.find({$or:db.notification.find({'startDate': { '$gte': ISODate("2019-08-01") }},{_id:1}).limit(10).toArray()})
Starlton
  • 429
  • 5
  • 14