MongoDB - $lookup in complex nested array

Question

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

Courses

{
  "_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
        },      
      ]
    },
  ]
}

Modules (only one as example)

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

Can anybody help me?

Answer 1

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

Working Example