$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屋！