Join multiple collections after parallel aggregation in Mongodb

Question

I have a collection called "Reel" which has embedded Objects.

{
    "_id":"reel_1",
    "category":[
        {
            "_id" : "cat_1",
            "videos": [ {"_id":"vid_1"},{"_id":"vid_2"} ] //_id is reference of Video collection
        },
        {
            "_id" : "cat_2",
            "videos": [ {"_id":"vid_3"},{"_id":"vid_4"} ]
        }
    ]
}

Video is another collection whose _id is referred inside reel-> category -> videos -> _id

{
    "_id":"vid_1",
    "title":"title 1",
    "groups":[{"_id":"group_1"},{"_id":"group_2"}]
},
{
    "_id":"vid_2",
    "title":"title 2",
    "groups":[{"_id":"group_1"},{"_id":"group_4"}]
},
{
    "_id":"vid_3",
    "title":"title 3",
    "groups":[{"_id":"group_1"},{"_id":"group_2"}]
},
{
    "_id":"vid_4",
    "title":"title 4",
    "groups":[{"_id":"group_3"},{"_id":"group_4"}]
}

The Document collection which holds _id of Reel and _id of Category

{
    "_id":"doc_1",
    "title":"document title",
    "assessments":[
        {
            "reel":"reel_1",   // reel reference _id
            "category":"cat_1", // category reference _id
            "groups":[{"_id":"group_1"},{"_id":"group_2"}
            ]
        }
    ]
    
}

I need to join and find all related embedded Objects which has group_1. I have done joining between Reel collection and Video collection and working fine,

{ $unwind: { path: '$category', preserveNullAndEmptyArrays: true }}, 
{ $unwind: { path: '$category.videos', preserveNullAndEmptyArrays: true }}, 
{
    $lookup: {
        from: 'video',
        localField: 'category.videos._id',
        foreignField: '_id',
        as: 'joinVideo'
    }
}, 
{ $unwind: { path: "$joinVideo", preserveNullAndEmptyArrays: true }}, 
{ $unwind: { path: "$joinVideo.groups", preserveNullAndEmptyArrays: true }}, 
{ $match: { "joinVideo.groups._id": "group_1" }}, 
{ $addFields: { "category.videos": "$joinVideo" }}, 
{
    $group: {
        _id: {
            _id: "$_id",
            category: "$category._id"
        },
        videos: {
            $addToSet: "$category.videos"
        }
    }
}, {
    $group: {
        _id: "$_id._id",
        category: {
            $addToSet: {
                "_id": "$_id.category",
                "videos": "$videos"
            }
        }
    }
}

The document collection should be embedded inside the category object based on reel _id and and category _id filtered by group_1. My expected result is

{
    "_id":"reel_1",
    "category":[
        {
            "_id" : "cat_1",
            "videos": [
                {
                    "_id":"vid_1",
                    "title":"title 1",
                    "groups":[ {"_id":"group_1"},{"_id":"group_2"}]
                },  
                {
                    "_id":"vid_2",
                    "title":"title 2",
                    "groups":[{"_id":"group_1"},{"_id":"group_4"}]
                }   
            ],
            "documents":[
                { // this document comes by reel="reel_1", category="cat_1", filtered by "group_1"
                    "_id":"doc_1",
                    "title":"document title",
                }
            ]
        },
        {
            "_id" : "cat_2",
            "videos": [
                {
                    "_id":"vid_3",
                    "title":"title 3",
                    "groups":[{"_id":"group_1"},{"_id":"group_2"}]
                }               
            ]
        }
    ]
}

I tried in many ways. Since I'm new to Mongodb, I couldn't sort this out.

Answer 1

Since MongoDB v3.6, $lookup allows perform uncorrelated sub-queries. This allows us perform non-standard queries to join two or more collections.

Note: Explanation why we need to use $expr inside $lookup pipeline

---

Explanation

1. We apply $unwind to flatten $category

2. We perform $lookup with 2 conditions:

   video.groups._id == 'group_1' and video._id in reel.category.videos._id

Since $reel.category.videos._id returns an array, we need to use $in operator

3. Again we perform $lookup with 2 conditions. It creates documents field for every document
4. To remove fields dynamically, we need to use Aggregation expressions called $$REMOVE which allows us exclude conditionally a field from document
5. We perform $group stage to transform into desired result

---

db.reel.aggregate([
  {
    $unwind: {
      path: "$category",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $lookup: {
      from: "video",
      let: {
        videos: "$category.videos._id"
      },
      pipeline: [
        {
          $match: {
            "groups._id": "group_1",
            $expr: {
              $in: [
                "$_id",
                "$$videos"
              ]
            }
          }
        }
      ],
      as: "category.videos"
    }
  },
  {
    $lookup: {
      from: "document",
      let: {
        reel_id: "$_id",
        category_id: "$category._id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $in: [
                    "$$reel_id",
                    "$assessments.reel"
                  ]
                },
                {
                  $in: [
                    "$$category_id",
                    "$assessments.category"
                  ]
                }
              ]
            }
          }
        },
        {
          $project: {
            _id: 1,
            title: 1
          }
        }
      ],
      as: "category.documents"
    }
  },
  {
    $addFields: {
      "category.documents": {
        $cond: [
          {
            $eq: [
              {
                $size: "$category.documents"
              },
              0
            ]
          },
          "$$REMOVE",
          "$category.documents"
        ]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      category: {
        $push: "$category"
      }
    }
  }
])

MongoPlayground