我如何在MongoDB中执行SQL Join等效?
例如,假设你有两个集合(用户和评论),我想拉pid=444的所有评论以及每个评论的用户信息。
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" }
是否有一种方法可以一次性提取具有特定字段的所有评论(例如. ...find({pid:444}))和与每个评论相关的用户信息?
目前,我首先获取符合条件的注释,然后找出结果集中的所有uid,获取用户对象,并将它们与注释的结果合并。看来我做错了。
mongodb官方网站上的这个页面恰好解决了这个问题:
https://mongodb-documentation.readthedocs.io/en/latest/ecosystem/tutorial/model-data-for-ruby-on-rails.html
When we display our list of stories, we'll need to show the name of the user who posted the story. If we were using a relational database, we could perform a join on users and stores, and get all our objects in a single query. But MongoDB does not support joins and so, at times, requires bit of denormalization. Here, this means caching the 'username' attribute.
Relational purists may be feeling uneasy already, as if we were violating some universal law. But let’s bear in mind that MongoDB collections are not equivalent to relational tables; each serves a unique design objective. A normalized table provides an atomic, isolated chunk of data. A document, however, more closely represents an object as a whole. In the case of a social news site, it can be argued that a username is intrinsic to the story being posted.
查找美元(聚合)
对同一数据库中的未分片集合执行左外连接,以从“已连接”集合中筛选文档进行处理。$查找阶段向每个输入文档添加一个新的数组字段,其元素是“已加入”集合中的匹配文档。$查找阶段将这些重新塑造的文档传递给下一个阶段。
$查找阶段的语法如下:
平等的比赛
要在输入文档中的字段与" joined "集合中的文档中的字段之间执行相等匹配,$lookup stage的语法如下:
{
$lookup:
{
from: <collection to join>,
localField: <field from the input documents>,
foreignField: <field from the documents of the "from" collection>,
as: <output array field>
}
}
该操作将对应于以下伪sql语句:
SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (SELECT <documents as determined from the pipeline>
FROM <collection to join>
WHERE <pipeline> );
蒙哥URL