如何在 MongoDB 中执行等效的 SQL Join? [英] How do I perform the SQL Join equivalent in MongoDB?

问题描述

如何在 MongoDB 中执行等效的 SQL Join?

例如，假设您有两个集合(用户和评论)，我想提取 pid=444 的所有评论以及每个集合的用户信息.

评论{ uid:12345, pid:444, comment="blah" }{ uid:12345, pid:888, comment="asdf" }{ uid:99999, pid:444, comment="qwer" }用户{ uid:12345, name:"john" }{ uid:99999, name:"mia" }

有没有办法一次性拉取某个字段的所有评论(例如 ...find({pid:444}) )以及与每个评论相关的用户信息?

目前，我首先获取符合我的条件的评论，然后找出该结果集中的所有 uid，获取用户对象，并将它们与评论的结果合并.好像我做错了.

解决方案

从 Mongo 3.2 开始，这个问题的答案大多不再正确.添加到聚合管道的新 $lookup 运算符本质上与左外连接相同:

https://docs.mongodb.org/master/reference/operator/aggregation/lookup/#pipe._S_lookup

来自文档:

<代码>{$查找:{来自:<要加入的收藏>,localField:<来自输入文档的字段>，foreignField:<来自from"集合的文档的字段>，如:<输出数组字段>}}

当然，Mongo 不是关系数据库，开发人员正在谨慎地推荐 $lookup 的特定用例，但至少从 3.2 开始，MongoDB 现在可以进行连接了.>

How do I perform the SQL Join equivalent in MongoDB?

For example say you have two collections (users and comments) and I want to pull all the comments with pid=444 along with the user info for each.

comments
  { uid:12345, pid:444, comment="blah" }
  { uid:12345, pid:888, comment="asdf" }
  { uid:99999, pid:444, comment="qwer" }

users
  { uid:12345, name:"john" }
  { uid:99999, name:"mia"  }

Is there a way to pull all the comments with a certain field (eg. ...find({pid:444}) ) and the user information associated with each comment in one go?

At the moment, I am first getting the comments which match my criteria, then figuring out all the uid's in that result set, getting the user objects, and merging them with the comment's results. Seems like I am doing it wrong.

解决方案

As of Mongo 3.2 the answers to this question are mostly no longer correct. The new $lookup operator added to the aggregation pipeline is essentially identical to a left outer join:

https://docs.mongodb.org/master/reference/operator/aggregation/lookup/#pipe._S_lookup

From the docs:

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

Of course Mongo is not a relational database, and the devs are being careful to recommend specific use cases for $lookup, but at least as of 3.2 doing join is now possible with MongoDB.

这篇关于如何在 MongoDB 中执行等效的 SQL Join?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！