MongoDB recursively get documents using multipe fields

Using Mongo 6.0 in a NodeJS application, I have a collection where each document can have an array of mandatoryCourseIds or electiveCourseIds or both.

A sample could look like this:

[
    {
      "_id": 1,
      "mandatoryCourseIds": [
        "A",
        "B"
      ],
      "electiveCourseIds": []
    },
    {
      "_id": 2,
      "mandatoryCourseIds": [
        "B",
        "C"
      ]
    },
    {
      "_id": 3,
      "mandatoryCourseIds": [
        "C",
        "D"
      ]
    },
    {
      "_id": 4,
      "electiveCourseIds": [
        "D"
      ]
    },
    {
      "_id": 5,
      "mandatoryCourseIds": [
        "D"
      ]
    },
    {
      "_id": 6,
      "electiveCourseIds": [
        "F"
      ]
    },
    {
      "_id": 7,
      "mandatoryCourseIds": [
        "G"
      ],
      "electiveCourseIds": [
        "F"
      ]
    },
    {
      "_id": 8,
      "mandatoryCourseIds": [
        "H"
      ]
    },
    {
      "_id": 9,
      "electiveCourseIds": [
        "H",
        "I"
      ]
    }
  ]

Now I want a query which takes an array of _ids (let's say 1 and 8), fetches the documents with those _ids and then recursively returns all documents which have common courses - no matter if they are in mandatoryCourseIds or electiveCourseIds. So, for the example input 1 and 8, I expect to get

• 1 (because direct _id match)
• 2 (because shares mandatoryCourseIds B with 1)
• 3 (because shares mandatoryCourseIds C with 2)
• 4 (because shares electiveCourseIds D with 3)
• 5 (because shares mandatoryCourseIds E with 4)
• 8 (because direct _id match)
• 9 (because shares electiveCourseIds H with 8)

What I go so far is

db.myCollection.aggregate([
  {
    "$match": {
      _id: {
        $in: [
          1,
          8
        ]
      }
    }
  },
  {
    $project: {
      combined: {
        $concatArrays: [
          {
            $ifNull: [
              "$mandatoryCourseIds",
              []
            ]
          },
          {
            $ifNull: [
              "$electiveCourseIds",
              []
            ]
          }
        ]
      }
    }
  },
  {
    $graphLookup: {
      from: "myCollection",
      startWith: "$combined",
      connectFromField: "mandatoryCourseIds",
      connectToField: "mandatoryCourseIds",
      as: "recursiveInfo"
    }
  }
])

The problem is of course that $graphLookup only accepts one field as connectFromField and connectToField. I would need two: mandatoryCourseIds and electiveCourseIds.

Furthermore, it seems that while $lookup supports $documents stage instead of from, $graphLookup does not. So, I could not figure out how to first combine all mandatoryCourseIds and electiveCourseIds into one array and then use it for $graphLookup. How do I solve this?

You can create a view, say named courseView, to combine the courseId first. Then perform the $graphLookup on the view to get your desired result.

db.createView(
  "courseView",
  "course",
  [
  {
    "$set": {
      "combined": {
        "$setUnion": [
          "$mandatoryCourseIds",
          "$electiveCourseIds"
        ]
      }
    }
  }
]
)

Perform $graphLookup on the view.

db.courseView.aggregate([
  {
    "$match": {
      _id: {
        $in: [
          1,
          8
        ]
      }
    }
  },
  {
    $graphLookup: {
      from: "courseView",
      startWith: "$combined",
      connectFromField: "combined",
      connectToField: "combined",
      as: "recursiveInfo"
    }
  },
  {
    "$unwind": "$recursiveInfo"
  },
  {
    "$replaceRoot": {
      "newRoot": "$recursiveInfo"
    }
  }
])

Mongo Playground