在 MongoDB 中搜索多个集合

Search on multiple collections in MongoDB

提问by Adrian Istrate

I know the theory of MongoDB and the fact that is doesn't support joins, and that I should use embeded documents or denormalize as much as possible, but here goes:

我知道 MongoDB 的理论和不支持连接的事实，我应该尽可能多地使用嵌入文档或非规范化，但这里是：

I have multiple documents, such as:

我有多个文件，例如：

• Users, which embed Suburbs, but also has: first name, last name
• Suburbs, which embed States
• Child, which embeds School, belongs to a User, but also has: first name, last name

• 嵌入郊区的用户，但也有：名字，姓氏
• 郊区，其中嵌入了国家
• 嵌入学校的孩子属于一个用户，但也有：名字，姓氏

Example:

例子：

Users:
{ _id: 1, first_name: 'Bill', last_name: 'Gates', suburb: 1 }
{ _id: 2, first_name: 'Steve', last_name: 'Jobs', suburb: 3 }

Suburb:
{ _id: 1, name: 'Suburb A', state: 1 }
{ _id: 2, name: 'Suburb B', state: 1 }
{ _id: 3, name: 'Suburb C', state: 3 }

State:
{ _id: 1, name: 'LA' }
{ _id: 3, name: 'NY' }

Child:
{ _id: 1, _user_id: 1, first_name: 'Little Billy', last_name: 'Gates' }
{ _id: 2, _user_id: 2, first_name: 'Little Stevie', last_name: 'Jobs' }

The search I need to implement is on:

我需要实施的搜索是：

• first name, last name of Users and Child
• State from Users

• 用户和孩子的名字、姓氏
• 来自用户的状态

I know that I have to do multiple queries to get it done, but how can that be achieved? With mapReduce or aggregate?

我知道我必须做多个查询才能完成它，但是如何实现呢？使用 mapReduce 还是聚合？

Can you point out a solution please?

你能指出一个解决方案吗？

I've tried to use mapReduce but that didn't get me to have documents from Users which contained a state_id, so that's why I brought it up here.

我尝试使用 mapReduce 但这并没有让我从包含 state_id 的用户那里获得文档，所以这就是我把它带到这里的原因。

回答by Philipp

This answer is outdated. Since version 3.2, MongoDB has limited support for left outer joins with the $lookupaggregation operator

这个答案已经过时了。从 3.2 版开始，MongoDB 对使用$lookup聚合运算符的左外连接的支持有限

MongoDB does not do queries which span multiple collections - period. When you need to join data from multiple collections, you have to do it on the application level by doing multiple queries.

MongoDB 不执行跨越多个集合的查询 - 期间。当您需要连接来自多个集合的数据时，您必须在应用程序级别通过执行多个查询来完成。

1. Query collection A
2. Get the secondary keys from the result and put them into an array
3. Query collection B passing that array as the value of the $in-operator
4. Join the results of both queries programmatically on the application layer

1. 查询集合A
2. 从结果中获取辅助键并将它们放入数组中
3. 查询集合 B 将该数组作为$in-operator的值传递
4. 在应用层以编程方式连接两个查询的结果

Having to do this should be rather the exception than the norm. When you frequently need to emulate JOINs like that, it either means that you are still thinking too relational when you design your database schema or that your data is simply not suited for the document-based storage concept of MongoDB.

必须这样做应该是例外而不是常态。当您经常需要模拟这样的 JOIN 时，这要么意味着您在设计数据库架构时仍然考虑过于相关，或者您的数据根本不适合 MongoDB 的基于文档的存储概念。

回答by blimpyacht

You'll find MongoDB easier to understand if you take a denormalized approach to schema design. That is, you want to structure your documents the way the requesting client application understands them. Essentially, you are modeling your documents as domain objectswith which the applicaiton deals. Joins become less important when you model your data this way. Consider how I've denormalized your data into a single collection:

如果您采用非规范化的模式设计方法，您会发现 MongoDB 更容易理解。也就是说，您希望按照请求客户端应用程序理解它们的方式来构建文档。本质上，您将文档建模为应用程序处理的域对象。当您以这种方式对数据建模时，联接变得不那么重要。考虑一下我如何将您的数据非规范化为一个集合：

{  
    _id: 1, 
    first_name: 'Bill', 
    last_name: 'Gates', 
    suburb: 'Suburb A',
    state: 'LA',
    child : [ 3 ]
}

{ 
    _id: 2, 
    first_name: 'Steve', 
    last_name: 'Jobs', 
    suburb: 'Suburb C',
    state 'NY',
    child: [ 4 ] 
}
{ 
    _id: 3, 
    first_name: 'Little Billy', 
    last_name: 'Gates',
    suburb: 'Suburb A',
    state: 'LA',
    parent : [ 1 ]
}

{
    _id: 4, 
    first_name: 'Little Stevie', 
    last_name: 'Jobs'
    suburb: 'Suburb C',
    state 'NY',
    parent: [ 2 ]
}

The first advantage is that this schema is far easier to query. Plus, updates to address fields are now consistent with the individual Person entity since the fields are embedded in a single document. Notice also the bidirectional relationship between parent and children? This makes this collection more than just a collection of individual people. The parent-child relationships mean this collection is also a social graph. Here are some resoures which may be helpful to you when thinking about schema design in MongoDB.

第一个优点是这种模式更容易查询。此外，地址字段的更新现在与单个 Person 实体一致，因为这些字段嵌入在单个文档中。还注意到父子之间的双向关系吗？这使得这个集合不仅仅是个人的集合。父子关系意味着这个集合也是一个社交图。以下是一些资源，可能对您在 MongoDB 中考虑模式设计有所帮助。

回答by Brian Moquin

Here's a JavaScript function that will return an array of all records matching specified criteria, searching across all collections in the current database:

这是一个 JavaScript 函数，它将返回匹配指定条件的所有记录的数组，搜索当前数据库中的所有集合：

function searchAll(query,fields,sort) {
    var all = db.getCollectionNames();
    var results = [];
    for (var i in all) {
        var coll = all[i];
        if (coll == "system.indexes") continue;
        db[coll].find(query,fields).sort(sort).forEach(
            function (rec) {results.push(rec);} );
    }
    return results;
}

From the Mongo shell, you can copy/paste the function in, then call it like so:

在 Mongo shell 中，您可以复制/粘贴该函数，然后像这样调用它：

> var recs = searchAll( {filename: {$regex:'.pdf$'} }, {moddate:1,filename:1,_id:0}, {filename:1} ) > recs

> var recs = searchAll( {filename: {$regex:'.pdf$'} }, {moddate:1,filename:1,_id:0}, {filename:1} ) > recs

回答by Ashh

So now join is possible in mongodb and you can achieve this using $lookupand $facetaggregation here and which is probably the best way to find in multiple collections

所以现在在 mongodb 中加入是可能的，你可以在这里使用$lookup和$facet聚合来实现这一点，这可能是在多个集合中查找的最佳方式

db.collection.aggregate([
  { "$limit": 1 },
  { "$facet": {
    "c1": [
      { "$lookup": {
        "from": Users.collection.name,
        "pipeline": [
          { "$match": { "first_name": "your_search_data" } }
        ],
        "as": "collection1"
      }}
    ],
    "c2": [
      { "$lookup": {
        "from": State.collection.name,
        "pipeline": [
          { "$match": { "name": "your_search_data" } }
        ],
        "as": "collection2"
      }}
    ],
    "c3": [
      { "$lookup": {
        "from": State.collection.name,
        "pipeline": [
          { "$match": { "name": "your_search_data" } }
        ],
        "as": "collection3"
      }}
    ]
  }},
  { "$project": {
    "data": {
      "$concatArrays": [ "$c1", "$c2", "$c3" ]
    }
  }},
  { "$unwind": "$data" },
  { "$replaceRoot": { "newRoot": "$data" } }
])

回答by Minime

Based on @brian-moquin and others, I made a set of functions to search entire collections with entire keys(fields) by simple keyword.

基于@brian-moquin 和其他人，我制作了一组函数来通过简单的关键字搜索具有整个键（字段）的整个集合。

It's in my gist; https://gist.github.com/fkiller/005dc8a07eaa3321110b3e5753dda71b

这是我的要旨；https://gist.github.com/fkiller/005dc8a07eaa3321110b3e5753dda71b

For more detail, I first made a function to gather all keys.

有关更多详细信息，我首先创建了一个函数来收集所有键。

function keys(collectionName) {
    mr = db.runCommand({
        'mapreduce': collectionName,
        'map': function () {
            for (var key in this) { emit(key, null); }
        },
        'reduce': function (key, stuff) { return null; },
        'out': 'my_collection' + '_keys'
    });
    return db[mr.result].distinct('_id');
}

Then one more to generate $orquery from keys array.

然后再$or从键数组生成查询。

function createOR(fieldNames, keyword) {
    var query = [];
    fieldNames.forEach(function (item) {
        var temp = {};
        temp[item] = { $regex: '.*' + keyword + '.*' };
        query.push(temp);
    });
    if (query.length == 0) return false;
    return { $or: query };
}

Below is a function to search a single collection.

下面是一个搜索单个集合的函数。

function findany(collection, keyword) {
    var query = createOR(keys(collection.getName()));
    if (query) {
        return collection.findOne(query, keyword);
    } else {
        return false;
    }
}

And, finally a search function for every collections.

而且，最后是每个集合的搜索功能。

function searchAll(keyword) {
    var all = db.getCollectionNames();
    var results = [];
    all.forEach(function (collectionName) {
        print(collectionName);
        if (db[collectionName]) results.push(findany(db[collectionName], keyword));
    });
    return results;
}

You can simply load all functions in Mongo console, and execute searchAll('any keyword')

您可以简单地在 Mongo 控制台中加载所有功能，然后执行 searchAll('any keyword')

回答by Shaikh Arbaaz

You can achieve this using $mergeObjects by MongoDB Driver Example Create a collection orders with the following documents:

您可以通过 MongoDB Driver Example 使用 $mergeObjects 实现此目的使用以下文档创建集合订单：

db.orders.insert([
  { "_id" : 1, "item" : "abc", "price" : 12, "ordered" : 2 },
  { "_id" : 2, "item" : "jkl", "price" : 20, "ordered" : 1 }
])

Create another collection items with the following documents:

使用以下文档创建另一个集合项：

db.items.insert([
  { "_id" : 1, "item" : "abc", description: "product 1", "instock" : 120 },
  { "_id" : 2, "item" : "def", description: "product 2", "instock" : 80 },
  { "_id" : 3, "item" : "jkl", description: "product 3", "instock" : 60 }
])

The following operation first uses the $lookup stage to join the two collections by the item fields and then uses $mergeObjects in the $replaceRoot to merge the joined documents from items and orders:

下面的操作首先使用 $lookup 阶段通过 item 字段连接两个集合，然后使用 $replaceRoot 中的 $mergeObjects 从 items 和 orders 合并连接的文档：

db.orders.aggregate([
   {
      $lookup: {
         from: "items",
         localField: "item",    // field in the orders collection
         foreignField: "item",  // field in the items collection
         as: "fromItems"
      }
   },
   {
      $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$fromItems", 0 ] }, "$$ROOT" ] } }
   },
   { $project: { fromItems: 0 } }
])

The operation returns the following documents:

该操作返回以下文档：

{ "_id" : 1, "item" : "abc", "description" : "product 1", "instock" : 120, "price" : 12, "ordered" : 2 }
{ "_id" : 2, "item" : "jkl", "description" : "product 3", "instock" : 60, "price" : 20, "ordered" : 1 }

This Technique merge Object and return the result

此技术合并对象并返回结果

回答by Onkar Parmar

Minime solution worked except that it required a fix: var query = createOR(keys(collection.getName())); need to add keyword as 2nd parameter to createOR call here.

Minime 解决方案有效，但它需要修复： var query = createOR(keys(collection.getName())); 需要添加关键字作为第二个参数来创建或调用这里。