MongoDB-复杂嵌套数组中的$ lookup [英] MongoDB - $lookup in complex nested array

问题描述

我正在mongodb中构建一个新的LMS，并且具有以下集合:

I'm building a new LMS in mongodb and I have the following collections:

课程

{
  "_id": ObjectId("5f6a6b159de1304fb885b194"),
  "title": "Course test",
  "sections": [
    {
      "_id": ObjectId("5f6a6b159de1304fb885b195"),
      "title": "Section 1 - introduction",
      "order": 1,
      "modules": [
        {
          "_id": ObjectId("5f6a6b159de1304fb885b196"),
          "module_FK_id": ObjectId("5f6a6b149de1304fb885b135"),
          "title": "Module 1",
          "order": 1
        },
        {
          "_id": ObjectId("5f6a6b159de1304fb885b198"),
          "module_FK_id": ObjectId("5f6a6b149de1304fb885b14a"),
          "title": "Module 2",
          "order": 2
        },      
      ]
    },
    {
      "_id": ObjectId("5f6a6b149de1304fb885b175"),
      "title": "Section 2 - How to do something",
      "order": 2,
      "modules": [
        {
          "_id": ObjectId("5f6a6b149de1304fb885b141"),
          "module_FK_id": ObjectId("5f6a6b149de1304fb885b150"),
          "title": "Module 1",
          "order": 1
        },
        {
          "_id": ObjectId("5f6a6b149de1304fb885b15f"),
          "module_FK_id": ObjectId("5f6a6b149de1304fb885b18e"),
          "title": "Module 2",
          "order": 2
        },      
      ]
    },
  ]
}

模块(仅一个示例)

{
  "_id": ObjectId("5f6a6b149de1304fb885b135"),
  "text": "Lorem ipsum...",
  "mediaUrl": "urllinkhere"
}

如图所示，我选择为部分和模块标题嵌入文档，但是我还需要第二个集合模块，因为每个模块包含大量文本，并且我的课程文档可能很快变得太大.

As shown, I choose to have embedded documents for sections and modules titles but I need also a second collection, modules, because each module contains a large amount of text and my course document may get too big quickly.

现在，我需要重建整个文档，就像它完全被嵌入一样.这是一个示例:

Now I need to rebuild the entire document as if it was completely embedded. Here's an example:

   {
      "_id": ObjectId("5f6a6b159de1304fb885b194"),
      "title": "Course test",
      "sections": [
        {
          "_id": ObjectId("5f6a6b159de1304fb885b195"),
          "title": "Section 1 - introduction",
          "order": 1,
          "modules": [
            {
              "_id": ObjectId("5f6a6b159de1304fb885b196"),
              "module_FK_id": ObjectId("5f6a6b149de1304fb885b135"),
              "title": "Module 1",
              "order": 1
              "text": "Lorem ipsum...",
              "mediaUrl": "urllinkhere"
            },
            // last two fields from collection "modules"

我正在尝试聚合和查找的不同组合，但无法获得所需的结果.

I'm trying different combination of aggregation and lookups but I can't obtain the desired result.

有人可以帮助我吗?

推荐答案

您可以像下面这样构建聚合管道，请记住要 $ group $ unwind operations

You can construct the aggregation pipeline like this below, just remember to $group in the reverse order of $unwind operations

db.courses.aggregate([
  {
    $unwind: "$sections"
  },
  {
    $unwind: "$sections.modules"
  },
  {
    "$lookup": {
      "from": "modules",
      "localField": "sections.modules.module_FK_id",
      "foreignField": "_id",
      "as": "module_details"
    }
  },
  {
    $unwind: {
      path: "$module_details",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    "$project": {
      title: 1,
      sections: {
        _id: "$sections._id",
        modules: {
          _id: "$sections.modules._id",
          module_FK_id: "$sections.modules.module_FK_id",
          order: "$sections.modules.order",
          title: "$sections.modules.title",
          mediaUrl: "$module_details.mediaUrl",
          text: "$module_details.text"
        },
        order: "$sections.order",
        title: "$sections.title"
      }
    }
  },
  {
    "$group": {
      "_id": "$sections._id",
      main_id: {
        $first: "$_id"
      },
      main_title: {
        $first: "$title"
      },
      order: {
        $first: "$sections.order"
      },
      title: {
        $first: "$sections.title"
      },
      modules: {
        $push: "$sections.modules"
      }
    }
  },
  {
    "$project": {
      "_id": "$main_id",
      "title": "$main_title",
      section: {
        _id: "$_id",
        modules: "$modules",
        title: "$title",
        order: "$order",
        
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      title: {
        $first: "$title"
      },
      sections: {
        $push: "$section"
      }
    }
  }
])

工作示例

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