如何在 MongoDb 中进行嵌套查询，其工作方式类似于嵌套 Sql 选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9883617/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make nested queries in MongoDb that works like nested Sql select queries
提问by ssn
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:
我想在 MongoDb 中进行有效的查询，以查找在用户组中列出其用户 ID 的所有用户。理想情况下，我想将此作为对 Mongodb 的单个请求。我想要的对应于 SQL 中的嵌套选择。我在 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:
现在这是我想要做的选择，但没有对 ["u2","u3"] 数组进行硬编码：
db.user.find({_id:{$in:["u2","u3"]}}).forEach(printjson);
This works fine and returns the user objects for u2 and u3.
这工作正常并返回 u2 和 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.
现在的问题是如何获取 $in 操作符中使用查询提取的用户 ID 数组，以便可以通过单个请求进行整个查询。
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
给出这个错误：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 ?
1) 这在 mongodb 中是可能的吗？
2) how to do this with the official c# driver ?
2) 如何使用官方 c# 驱动程序执行此操作？
采纳答案by Ian Mercer
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 andthe 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.
MongoDB 中此类问题的答案通常是对数据进行非规范化。如果您只需要组中的用户列表，您可以将用户 ID和用户名存储在组文档中。在某些方面，您可以根据希望在屏幕上看到的结果来构建数据库，而不是尝试将其置于某种规范化格式中。
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.
同样，您可以将组名与其 _id 一起存储，以便您可以通过一次往返立即显示用户所属的组列表。当然，如果您允许更改组名称，则必须启动后台任务来修复所有这些名称副本。
I would also use the built in MongoDB id generator rather than your own, it has many desirable properties.
我也会使用内置的 MongoDB id 生成器而不是你自己的，它有许多理想的属性。
回答by pippo
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()
回答by Markrings
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;
});
回答by Surendranath Reddy K
-sUReN
-sReN
SQL Query: (group by & count of distinct)
SQL 查询：（分组依据和不同计数）
select city,count(distinct(emailId)) from TransactionDetails group by city;
Equivalent mongo query would look like this:
等效的 mongo 查询如下所示：
db.TransactionDetails.aggregate([
{$group:{_id:{"CITY" : "$cityName"},uniqueCount: {$addToSet: "$emailId"}}},
{$project:{"CITY":1,uniqueCustomerCount:{$size:"$uniqueCount"}} }
]);
回答by Starlton
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()
我想知道关于 mongodb 中嵌套查询的同样事情。就我而言，我稍微放了一点并让它工作，我认为你也可以通过使用“$or”并使用 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:
可能不太正确，但这是我的数据的一个示例，该数据使用 nexted 查询。显然有点傻，为什么在这种情况下将 id 反馈回另一个查询，但想看看它是否有效，并且确实如此：
db.notification.find({$or:db.notification.find({'startDate': { '$gte': ISODate("2019-08-01") }},{_id:1}).limit(10).toArray()})
