如何在MongoDb中进行类似于嵌套SQL选择查询的嵌套查询 [英] How to make nested queries in MongoDb that works like nested Sql select queries

问题描述

我想在MongoDb中进行有效的查询，以找到在用户组中列出了其用户名的所有用户.理想情况下，我想将其作为对Mongodb的单个请求. 我想要的对应于SQL中的嵌套选择. 我已经在mongo shell中尝试过此操作:

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"]});

现在这是我要执行的选择，但是没有对["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);

这可以正常工作，并返回u2和u3的用户对象.

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

现在的问题是如何在查询中提取$ in运算符中的用户ID数组，以便可以通过单个请求进行整个查询.

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);

给出此错误: 3月27日，星期二06:17:41未捕获的异常:错误:{"$ err":无效的查询"，"code":12580} 加载失败:mongoNestedSelect.js

Gives this error: Tue Mar 27 06:17:41 uncaught exception: error: { "$err" : "invalid query", "code" : 12580 } failed to load: mongoNestedSelect.js

1)在mongodb中有可能吗?

1) is this possible in mongodb and how ?

2)如何使用官方C#驱动程序执行此操作?

2) how to do this with the official c# driver ?

推荐答案

在MongoDB中，此类问题的答案通常是对数据进行非规范化.如果只需要组中的用户列表，则可以在组文档中存储用户ID 和.在某些方面，您可以根据要在屏幕上看到的结果来构建数据库，而不是尝试以某种标准化的格式来放置数据库.

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"}, ...]});

同样，您可以使用其_id存储组名，以便您可以通过一次往返立即显示用户所属的组的列表.当然，如果您允许更改群组名称，则必须启动后台任务来修复名称的所有这些副本.

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.

我还将使用内置的MongoDB id生成器，而不是您自己的ID生成器，它具有许多理想的属性.

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

这篇关于如何在MongoDb中进行类似于嵌套SQL选择查询的嵌套查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！