MongoDB aggregate with array and nested lookup

Question

I'm struggling to put a query together that aggregates the data exactly as I want. I would be so grateful if anyone can help with this!

The collections are:

Collection 1

[
 {
   "_id": 1,
   "name": "Collection 1:1",
   "collection_2_ids": [5, 6]
 },
 {
   "_id": 2,
   "name": "Collection 1:2",
   "collection_2_ids": [8, 9]
 }
]

Collection 2

[
 {
   "_id": 5,
   "name": "collection 2:5",
   "collection_1_id": 1,
   "collection_3_id": 12
 },
 {
   "_id": 6,
   "name": "collection 2:6",
   "collection_1_id": 1,
   "collection_3_id": 13
 },
 {
   "_id": 8,
   "name": "collection 2:8",
   "collection_1_id": 2,
   "collection_3_id": 14
 },
 {
   "_id": 9,
   "name": "collection 2:9",
   "collection_1_id": 2,
   "collection_3_id": 15
 },
]

Collection 3:

[
 {
   "_id": 12,
   "name": "collection 3:12"
 },
 {
   "_id": 13,
   "name": "collection 3:13"
 },
 {
   "_id": 14,
   "name": "collection 3:14"
 },
 {
   "_id": 15,
   "name": "collection 3:15"
 }
]

What I want out of it is:

[
 {
   "_id": 1,
   "name": "Collection 1:1",
   "collection_2_documents": [
     {
       "_id": 5,
       "name": "collection 2:5",
       "collection_1_id": 1,
       "collection_3_id": 12,
       "collection_3_document": {
          "_id": 12,
          "name": "collection 3:12"
        }
     },
     {
       "_id": 6,
       "name": "collection 2:6",
       "collection_1_id": 1,
       "collection_3_id": 13,
       "collection_3_document": {
          "_id": 12,
          "name": "collection 3:12"
        }
     }
  ]
 },
 {
   "_id": 2,
   "name": "Collection 1:2",
   "collection_2_documents": [
       {
       "_id": 8,
       "name": "collection 2:8",
       "collection_1_id": 2,
       "collection_3_id": 14,
       "collection_3_document": {
          "_id": 14,
          "name": "collection 3:14"
        }
     },
     {
       "_id": 9,
       "name": "collection 2:9",
       "collection_1_id": 2,
       "collection_3_id": 15,
       "collection_3_document": {
          "_id": 15,
          "name": "collection 3:15"
        }
     }
   ]
 }
]

This is the current aggregate/lookup that I have which returns separate arrays of documents from collections 2 and 3.

[  
  {
    $lookup: {
      from: 'Collection 2',
      localField: 'collection_2_ids',
      foreignField: '_id',
      as: 'collection_2_documents'
    }
  },
  {
    $lookup: {
      from: 'Collection 3',
      localField: 'collection_2_documents.collection_3_id',
      foreignField: '_id',
      as: 'collection_3_document'
    }
  }
]

Answer 1

You have to make use of pipeline option of $lookup stage and use nested $lookup on collection3 inside collection2's lookup

db.collection1.aggregate([
  {
    "$lookup": {
      "from": "collection2",
      "let": {
        "sourceCollection_2_ids": "$collection_2_ids"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$_id",
                "$$sourceCollection_2_ids"
              ]
            }
          }
        },
        {
          "$lookup": {
            "from": "collection3",
            "let": {
              "sourceCollection_3_id": "$collection_3_id"
            },
            "pipeline": [
              {
                "$match": {
                  "$expr": {
                    "$eq": [
                      "$_id",
                      "$$sourceCollection_3_id"
                    ]
                  },
                  
                }
              },
              
            ],
            "as": "collection_3_document"
          }
        },
        {
          "$set": {
            "collection_3_document": {
              "$arrayElemAt": [
                "$collection_3_document",
                0
              ]
            }
          }
        },
        
      ],
      "as": "collection_2_documents"
    }
  },
  
])

Mongo Playground Sample Execution