mongodb $lookup 在数组中的 ObjectId 上
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34967482/
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
$lookup on ObjectId's in an array
提问by Jason Lin
What's the syntax for doing a $lookup on a field that is an array of ObjectIds rather than just a single ObjectId?
在 ObjectId 数组而不是单个 ObjectId 的字段上执行 $lookup 的语法是什么？
Example Order Document:
示例订单文件：
{
_id: ObjectId("..."),
products: [
ObjectId("..<Car ObjectId>.."),
ObjectId("..<Bike ObjectId>..")
]
}
Not Working Query:
不工作查询：
db.orders.aggregate([
{
$lookup:
{
from: "products",
localField: "products",
foreignField: "_id",
as: "productObjects"
}
}
])
Desired Result
想要的结果
{
_id: ObjectId("..."),
products: [
ObjectId("..<Car ObjectId>.."),
ObjectId("..<Bike ObjectId>..")
],
productObjects: [
{<Car Object>},
{<Bike Object>}
],
}
回答by Blakes Seven
2017 update
2017年更新
$lookup can now directly use an array as the local field. $unwindis no longer needed.
$lookup 现在可以直接使用数组作为本地字段。$unwind不再需要。
Old answer
旧答案
The $lookupaggregation pipeline stage will not work directly with an array. The main intent of the design is for a "left join" as a "one to many" type of join ( or really a "lookup" ) on the possible related data. But the value is intended to be singular and not an array.
该$lookup聚集流水线阶段不会与数组直接工作。设计的主要目的是将“左连接”作为对可能相关数据的“一对多”类型的连接（或实际上是“查找”）。但该值旨在是单数而不是数组。
Therefore you must "de-normalise" the content first prior to performing the $lookupoperation in order for this to work. And that means using $unwind:
因此，您必须在执行$lookup操作之前先对内容进行“反规范化” ，以使其正常工作。这意味着使用$unwind：
db.orders.aggregate([
// Unwind the source
{ "$unwind": "$products" },
// Do the lookup matching
{ "$lookup": {
"from": "products",
"localField": "products",
"foreignField": "_id",
"as": "productObjects"
}},
// Unwind the result arrays ( likely one or none )
{ "$unwind": "$productObjects" },
// Group back to arrays
{ "$group": {
"_id": "$_id",
"products": { "$push": "$products" },
"productObjects": { "$push": "$productObjects" }
}}
])
After $lookupmatches each array member the result is an array itself, so you $unwindagain and $groupto $pushnew arrays for the final result.
之后$lookup每个数组成员的比赛结果本身就是一个数组，让你$unwind再次$group来$push对最终结果新的阵列。
Note that any "left join" matches that are not found will create an empty array for the "productObjects" on the given product and thus negate the document for the "product" element when the second $unwindis called.
请注意，任何未找到的“左连接”匹配项将为给定产品上的“productObjects”创建一个空数组，从而在$unwind调用第二个元素时否定“product”元素的文档。
Though a direct application to an array would be nice, it's just how this currently works by matching a singular value to a possible many.
虽然直接应用到数组会很好，但这就是目前通过将奇异值与可能的多相匹配来工作的方式。
As $lookupis basically very new, it currently works as would be familiar to those who are familiar with mongooseas a "poor mans version" of the .populate()method offered there. The difference being that $lookupoffers "server side" processing of the "join" as opposed to on the client and that some of the "maturity" in $lookupis currently lacking from what .populate()offers ( such as interpolating the lookup directly on an array ).
由于$lookup基本上是非常新的，它目前的工作方式对于那些熟悉猫鼬的人来说是熟悉的，因为它是.populate()那里提供的方法的“穷人版本” 。不同之处在于它$lookup提供了“加入”的“服务器端”处理，而不是在客户端上，并且目前提供的某些“成熟度”$lookup缺乏.populate()（例如直接在数组上插入查找）。
This is actually an assigned issue for improvement SERVER-22881, so with some luck this would hit the next release or one soon after.
这实际上是改进SERVER-22881的指定问题，所以如果幸运的话，这将在下一个版本或不久之后发布。
As a design principle, your current structure is neither good or bad, but just subject to overheads when creating any "join". As such, the basic standing principle of MongoDB in inception applies, where if you "can" live with the data "pre-joined" in the one collection, then it is best to do so.
作为设计原则，您当前的结构既不好也不坏，只是在创建任何“连接”时受到开销的影响。因此，MongoDB 在开始时的基本原则适用，如果您“可以”使用一个集合中“预先加入”的数据，那么最好这样做。
The one other thing that can be said of $lookupas a general principle, is that the intent of the "join" here is to work the other way around than shown here. So rather than keeping the "related ids" of the other documents within the "parent" document, the general principle that works best is where the "related documents" contain a reference to the "parent".
另一件可以说$lookup是一般原则的另一件事是，此处“连接”的目的是与此处显示的相反。因此，与其将其他文档的“相关 id”保留在“父”文档中，最好的一般原则是“相关文档”包含对“父”的引用。
So $lookupcan be said to "work best" with a "relation design" that is the reverse of how something like mongoose .populate()performs it's client side joins. By idendifying the "one" within each "many" instead, then you just pull in the related items without needing to $unwindthe array first.
所以$lookup可以说“关系设计”是“最好的工作”，这与猫鼬.populate()之类的东西执行它的客户端连接的方式相反。通过在每个“多个”中标识“一个”，然后您只需拉入相关项目，而无需先$unwind使用数组。
回答by joseaio
Starting with MongoDB v3.4 (released in 2016), the $lookupaggregation pipeline stage can also work directly with an array. There is no need for $unwindany more.
从 MongoDB v3.4（2016 年发布）开始，$lookup聚合管道阶段也可以直接使用数组。没有必要了$unwind。
This was tracked in SERVER-22881.
这在SERVER-22881 中进行了跟踪。
回答by user12164
You can also use the pipelinestage to perform checks on a sub-docunment array
您还可以使用pipelinestage 对子文档数组执行检查
Here's the example using python(sorry I'm snake people).
这是使用的示例python（对不起，我是蛇人）。
db.products.aggregate([
{ '$lookup': {
'from': 'products',
'let': { 'pid': '$products' },
'pipeline': [
{ '$match': { '$expr': { '$in': ['$_id', '$$pid'] } } }
// Add additional stages here
],
'as':'productObjects'
}
])
The catch here is to match all objects in the ObjectIdarray(foreign _idthat is in localfield/prop products).
这里的问题是匹配ObjectIdarray（_id在localfield/prop中的外部）中的所有对象products。
You can also clean up or project the foreign records with additional stages, as indicated by the comment above.
您还可以使用其他stages清理或投影外部记录，如上面的评论所示。
回答by KARTHIKEYAN.A
use $unwindyou will get the first object instead of array of objects
使用$unwind您将获得第一个对象而不是对象数组
query:
询问：
db.getCollection('vehicles').aggregate([
{
$match: {
status: "AVAILABLE",
vehicleTypeId: {
$in: Array.from(newSet(d.vehicleTypeIds))
}
}
},
{
$lookup: {
from: "servicelocations",
localField: "locationId",
foreignField: "serviceLocationId",
as: "locations"
}
},
{
$unwind: "$locations"
}
]);
result:
结果：
{
"_id" : ObjectId("59c3983a647101ec58ddcf90"),
"vehicleId" : "45680",
"regionId" : 1.0,
"vehicleTypeId" : "10TONBOX",
"locationId" : "100",
"description" : "Isuzu/2003-10 Ton/Box",
"deviceId" : "",
"earliestStart" : 36000.0,
"latestArrival" : 54000.0,
"status" : "AVAILABLE",
"accountId" : 1.0,
"locations" : {
"_id" : ObjectId("59c3afeab7799c90ebb3291f"),
"serviceLocationId" : "100",
"regionId" : 1.0,
"zoneId" : "DXBZONE1",
"description" : "Masafi Park Al Quoz",
"locationPriority" : 1.0,
"accountTypeId" : 0.0,
"locationType" : "DEPOT",
"location" : {
"makani" : "",
"lat" : 25.123091,
"lng" : 55.21082
},
"deliveryDays" : "MTWRFSU",
"timeWindow" : {
"timeWindowTypeId" : "1"
},
"address1" : "",
"address2" : "",
"phone" : "",
"city" : "",
"county" : "",
"state" : "",
"country" : "",
"zipcode" : "",
"imageUrl" : "",
"contact" : {
"name" : "",
"email" : ""
},
"status" : "",
"createdBy" : "",
"updatedBy" : "",
"updateDate" : "",
"accountId" : 1.0,
"serviceTimeTypeId" : "1"
}
}
{
"_id" : ObjectId("59c3983a647101ec58ddcf91"),
"vehicleId" : "81765",
"regionId" : 1.0,
"vehicleTypeId" : "10TONBOX",
"locationId" : "100",
"description" : "Hino/2004-10 Ton/Box",
"deviceId" : "",
"earliestStart" : 36000.0,
"latestArrival" : 54000.0,
"status" : "AVAILABLE",
"accountId" : 1.0,
"locations" : {
"_id" : ObjectId("59c3afeab7799c90ebb3291f"),
"serviceLocationId" : "100",
"regionId" : 1.0,
"zoneId" : "DXBZONE1",
"description" : "Masafi Park Al Quoz",
"locationPriority" : 1.0,
"accountTypeId" : 0.0,
"locationType" : "DEPOT",
"location" : {
"makani" : "",
"lat" : 25.123091,
"lng" : 55.21082
},
"deliveryDays" : "MTWRFSU",
"timeWindow" : {
"timeWindowTypeId" : "1"
},
"address1" : "",
"address2" : "",
"phone" : "",
"city" : "",
"county" : "",
"state" : "",
"country" : "",
"zipcode" : "",
"imageUrl" : "",
"contact" : {
"name" : "",
"email" : ""
},
"status" : "",
"createdBy" : "",
"updatedBy" : "",
"updateDate" : "",
"accountId" : 1.0,
"serviceTimeTypeId" : "1"
}
}
回答by Archimedix
Aggregating with $lookupand subsequent $groupis pretty cumbersome, so if (and that's a medium if) you're using node & Mongoose or a supporting library with some hints in the schema, you could use a .populate()to fetch those documents:
聚合 with$lookup和后续$group非常麻烦，所以如果（如果这是一个媒介）你正在使用 node & Mongoose 或在模式中有一些提示的支持库，你可以使用 a.populate()来获取这些文档：
var mongoose = require("mongoose"),
Schema = mongoose.Schema;
var productSchema = Schema({ ... });
var orderSchema = Schema({
_id : Number,
products: [ { type: Schema.Types.ObjectId, ref: "Product" } ]
});
var Product = mongoose.model("Product", productSchema);
var Order = mongoose.model("Order", orderSchema);
...
Order
.find(...)
.populate("products")
...
回答by Liebster Kamerad
I have to disagree, we can make $lookup work with IDs array if we preface it with $match stage.
我不得不不同意，如果我们以 $match 阶段作为序言，我们可以使 $lookup 与 IDs 数组一起工作。
// replace IDs array with lookup results
db.products.aggregate([
{ $match: { products : { $exists: true } } },
{
$lookup: {
from: "products",
localField: "products",
foreignField: "_id",
as: "productObjects"
}
}
])It becomes more complicated if we want to pass the lookup result to a pipeline. But then again there's a way to do so (already suggested by @user12164):
如果我们想将查找结果传递给管道，就会变得更加复杂。但话又说回来，有一种方法可以这样做（@user12164 已经建议）：
// replace IDs array with lookup results passed to pipeline
db.products.aggregate([
{ $match: { products : { $exists: true } } },
{
$lookup: {
from: "products",
let: { products: "$products"},
pipeline: [
{ $match: { $expr: {$in: ["$_id", "$$products"] } } },
{ $project: {_id: 0} } // suppress _id
],
as: "productObjects"
}
}
])