$lookup 在数组中的 ObjectId 上 [英] $lookup on ObjectId's in an array

问题描述

对由 ObjectId 组成的数组而不是单个 ObjectId 的字段执行 $lookup 的语法是什么?

What's the syntax for doing a $lookup on a field that is an array of ObjectIds rather than just a single ObjectId?

示例订单文档:

{
  _id: ObjectId("..."),
  products: [
    ObjectId("..<Car ObjectId>.."),
    ObjectId("..<Bike ObjectId>..")
  ]
}

无效查询:

db.orders.aggregate([
    {
       $lookup:
         {
           from: "products",
           localField: "products",
           foreignField: "_id",
           as: "productObjects"
         }
    }
])

想要的结果

{
  _id: ObjectId("..."),
  products: [
    ObjectId("..<Car ObjectId>.."),
    ObjectId("..<Bike ObjectId>..")
  ],
  productObjects: [
    {<Car Object>},
    {<Bike Object>}
  ],
}

推荐答案

2017 更新

$lookup 现在可以直接使用数组作为本地字段.不再需要 $unwind.

$lookup 聚合管道阶段不会直接与数组一起工作.设计的主要目的是将左连接"作为可能相关数据的一对多"类型的连接(或实际上是查找").但该值是单一的，而不是数组.

The $lookup aggregation 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 操作之前先对内容进行反规范化"，以使其正常工作.这意味着使用 $unwind:

Therefore you must "de-normalise" the content first prior to performing the $lookup operation in order for this to work. And that means using $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" }
    }}
])

在 $lookup 匹配每个数组成员后，结果是一个数组本身，因此您再次$unwind 和 $group 到 $push 用于最终结果的新数组.

After $lookup matches each array member the result is an array itself, so you $unwind again and $group to $push new arrays for the final result.

请注意，任何未找到的左连接"匹配项都将为给定产品上的productObjects"创建一个空数组，从而在第二个 $unwind被调用.

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 $unwind is called.

虽然直接应用到数组会很好，但这就是目前通过将奇异值与可能的多相匹配来工作的方式.

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.

由于 $lookup 基本上是非常新的，它目前的工作方式对于熟悉 mongoose 作为那里提供的 .populate() 方法的穷人版".不同之处在于 $lookup 提供服务器端"处理加入"而不是在客户端，并且 $lookup 中的一些成熟度"目前是缺少 .populate() 提供的功能(例如直接在数组上插入查找).

As $lookup is basically very new, it currently works as would be familiar to those who are familiar with mongoose as a "poor mans version" of the .populate() method offered there. The difference being that $lookup offers "server side" processing of the "join" as opposed to on the client and that some of the "maturity" in $lookup is currently lacking from what .populate() offers ( such as interpolating the lookup directly on an array ).

这实际上是一个需要改进的指定问题SERVER-22881，所以祝你好运这将在下一个版本或不久之后发布.

This is actually an assigned issue for improvement SERVER-22881, so with some luck this would hit the next release or one soon after.

作为设计原则，您当前的结构没有好坏之分，只是在创建任何连接"时受到开销的影响.因此，MongoDB 在初始阶段的基本原则适用，如果您可以"使用一个集合中预先加入"的数据，那么最好这样做.

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.

$lookup 作为一般原则的另一件事是，此处加入"的目的是与此处显示的相反.因此，与其将其他文档的相关 id"保留在父"文档中，最好的一般原则是相关文档"包含对父"的引用.

The one other thing that can be said of $lookup as 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 可以说是工作最好"的关系设计"，这与 mongoose .populate() 之类的东西如何执行它的客户端相反侧连接.通过在每个多"中标识一个"，然后您只需拉入相关项目，而无需先$unwind 数组.

So $lookup can 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 $unwind the array first.

这篇关于$lookup 在数组中的 ObjectId 上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！