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

问题描述

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

订购文件示例:

{
  _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 聚合管道阶段将不起作用直接使用数组.设计的主要目的是针对左连接"，将其作为可能相关数据上的一对多"连接类型(或实际上是查找").但是该值应为单数而不是数组.

因此，必须先对内容进行反规范化"，然后才能执行$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" }
    }}
])
 

$lookup匹配每个数组成员后，结果是数组本身，因此您再次$unwind并 $push 新数组，以获得最终结果.

请注意，如果找不到任何左连接"匹配项，则会在给定产品上为"productObjects"创建一个空数组，从而在调用第二个$unwind时否定"product"元素的文档. /p>

虽然直接应用到数组会很好，但这正是通过将奇异值与可能的多个匹配来实现的.

由于$lookup基本上是一个非常新的东西，因此它现在可以像熟悉 mongoose 作为此处提供的.populate()方法的穷人版本".区别在于$lookup提供的是连接"的服务器端"处理，而不是客户端提供的处理，而$lookup提供的内容目前缺少某些成熟度"(例如，内插直接在数组上查找).

这实际上是分配的改进问题， SERVER-22881 ，因此有些幸运的是，它将在下一个版本或之后发布.

作为一种设计原则，您当前的结构既不是好事，也不是坏事，而在创建任何联接"时只会受到开销的影响.因此，MongoDB从一开始就具有基本的站立原则，如果您可以"使用一个集合中的预加入"数据，那么最好这样做.

作为一般原则，可以说$lookup的另一件事是，这里的"join"的意图是与这里显示的相反.因此，与其将其他文档的相关ID"保留在父"文档中，不如将最有效的一般原则是相关文档"包含对父"文档的引用.

因此，可以说$lookup在关系设计"下表现最佳"，这与像猫鼬.populate()这样的客户端联接的执行方式相反.通过在每个很多"中标识一个"，然后只需提取相关项目，而无需先$unwind数组.

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

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>}
  ],
}

解决方案

2017 update

$lookup can now directly use an array as the local field. $unwind is no longer needed.

Old answer

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.

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" }
    }}
])

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.

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.

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 ).

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

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.

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".

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.

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