MongoDB $lookup 值返回数组 [英] MongoDB $lookup Value Back into Array

问题描述

我有一个包含多个项目的文档，需要从另一个集合中查找更多信息以填写数据.

I have a document with multiple items, that need to lookup further information from another collection to fill out the data.

主文档:

{
  _id: ObjectID("5a30ff41af58f911946b122e"),
  SalesItems: [
    {
      SalesItemID: 1139,
      Quantity: 2,
      Amount: 3.00
    },
    {
      SalesItemID: 2549,
      Quantity: 1,
      Amount: 5.40
    }
  ]
}

然后是另一个名为 SalesItem 的集合，其中包含这些销售项目的详细信息.两份文件

And then another collection called SalesItem which contains the details for those sales items. Two Documents

{
  _id: 1139,
  Name: "Item #1",
  Price: 1.50
}
{
  _id: 2549,
  Name: "Item #2",
  Price: 5.40
}

我正在尝试在聚合中进行 $lookup 以将信息从 SalesItem 集合拉到我的主集合的 SalesItems 数组中.要获得如下所示的结果:

I am trying to do a $lookup in an aggregate to pull the info from the SalesItem Collection into the SalesItems Array of my main Collection. To get a result that looks something like this:

{
  _id: ObjectID("5a30ff41af58f911946b122e"),
  SalesItems: [
    {
      SalesItemID: 1139,
      Quantity: 2,
      Amount: 3.00,
      SalesItemInfo: {
        _id: 1139,
        Name: "Item #1",
        Price: 1.50
      }
    },
    {
      SalesItemID: 2549,
      Quantity: 1,
      Amount: 5.40,
      SalesItemInfo: {
        _id: 2549,
        Name: "Item #2",
        Price: 5.40
      }
    }
  ]
}

谁能告诉我这是否可行?

Can anyone tell me if this is possible?

我尝试了以下方法，但结果中的 SalesItems 对象中仅存在 SalesItemInfo.

I have tried the following but it results in only the SalesItemInfo being present in the SalesItems Object in the result.

aggregate([
    {
        $lookup: {
            from: "SalesItem",
            localField: "SalesItems.SalesItemID",
            foreignField: "_id",
            as: "SalesItems.SalesItemInfo"
        }
    }
])

对于这个例子，SalesItem 集合已经被大量简化，它实际上是一个大约 990 行的 JSON 文档，然后才被插入到 Mongo 中.在主表中，它将被复制大约 64000 次，因此我试图避免将信息嵌入到主文档中.

The SalesItem collection has been massively simplified for this example, it is actually around a 990 line JSON Document before being inserted into Mongo. And in the main table it would be replicated around 64 thousand times, hence why I am trying to avoid embedding the info into the main document.

对此的任何帮助将不胜感激.

Any help with this would be massively appreciated.

谢谢，丹尼尔.

推荐答案

在不同阶段摸索后设法解决了:

Managed to work it out after a bit more fumbling around with different stages:

aggregate([
{
  $unwind: "$SalesItems"  
},
{
    $lookup: {
        from: "SalesItem",
        localField: "SalesItems.SalesItemID",
        foreignField: "ID",
        as: "SalesItemDetails"
    }
},
{
    $group: {
        _id: "$_id",
        SalesItems: {
            $push: {
                SalesItemID: "$SalesItems.SalesItemID",
                SalesItemDetails: "$SalesItemDetails"
            }
        }
    }
}
])

这正是我想要的结果.

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