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.

score 2 · Accepted Answer · edited Sep 17 '20 at 07:04

2

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

We apply $unwind to flatten $category
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

Again we perform $lookup with 2 conditions. It creates documents field for every document
To remove fields dynamically, we need to use Aggregation expressions called $$REMOVE which allows us exclude conditionally a field from document
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

edited Sep 17 '20 at 07:04

halfer

19,824
17
99
186

answered May 26 '20 at 09:00

Valijon

12,667
4
34
67

Thank you so much, Awesome explanation. I just need to clariify, why dont we perform normal lookup between videos and reels? both collections are in a relationship. – varman May 26 '20 at 12:32
@varman We need to apply 2 conditions: `groups._id == "group_1" AND _id == category.videos._id`. Standard `$lookup` can only apply 1 condition, so we have to use `uncorrelated sub-queries`. Also we can perform standard `$lookup` in this case, but then we need to apply a lot of extra stages to remove undesired data... – Valijon May 26 '20 at 12:36
ndeed.. Thanks for the clear explanation. I wrote a very long query for first lookup. – varman May 26 '20 at 12:53
@varman You can use `$ifNull` or [$type](https://docs.mongodb.com/manual/reference/operator/query/type/#available-types) to handle possible errors – Valijon May 26 '20 at 12:53
I have a small problem, here **category** is an array , **videos** is a nested nested array. In your answer you just `$unwind` the category only, you didn't unwind the **videos**(Its still an array). https://stackoverflow.com/questions/63226464/mongodb-aggregate-lookup-with-a-query/63226946#63226946 this is one of the question I gave answer. But I needed to unwind the array.. why is that? – varman Aug 06 '20 at 16:22
@varman Hi. It's because you have multiple conditions based on parent document. In my answer, `videos` is a single variable from parent collection. In your answer, you need to check both `testReport.name` and `testReport.value`. – Valijon Aug 06 '20 at 19:07
Thank you Valijon. But https://stackoverflow.com/questions/63272936/add-a-field-to-lookup-result-in-mongodb/63277632#63277632 here I use one variable. If I don't unwind the array, i won't work. – varman Aug 07 '20 at 04:34
Honestly I thank to you, You are the only one reason, I'm now ok in mongodb and Aggregation in spring. Thank you very much sir. I do remember whenever I ask questions, you reply. I'm following like you in stackoverflow to answer most questions. – varman Aug 07 '20 at 04:38
@varman Excellent, it's very usefull to learn MongoDB answering challenges. In your last example, remove `$unwind` and change to `$in: ["$_id", "$$shareId"]` or directly use standard `$lookup` syntax, since `localField` accepts arrays as well. Glad to help you :) – Valijon Aug 07 '20 at 08:22
wow, great, Thank you so much. I understood that if we don't `unwind`, we can use `in`, if we `unwind`, we can use `eq`. Its based on the situation – varman Aug 07 '20 at 09:51

Join multiple collections after parallel aggregation in Mongodb

1 Answers1

Explanation