MongoDB - How to $lookup on multiple local fields

Question

I've to write an aggregation query in MongoDB where the $lookup from source collection to destination collection can be from multiple fields. i.e. multiple fields from the source collection (all IDs) can be linked to a single field in the target collection.

In other words, localfield in my $lookup has an $or condition:

Student:

{
  "_id": : {
    "$oid": "61c32b08c6056d28db6550b5"
  },
  "name": "Jack Sparrow",
  "course1": : {
    "$oid": "82d77b08c6056d28db65ca98"
  },
  "course2": : {
    "$oid": "45d22b08c6056d28db688013"
  },
  "course3": : {
    "$oid": "98a72b08c6056d28db6561d6"
  },
  "course4": : {
    "$oid": "10b22b08c6056d28db6576b1"
  }
}

Query:

{
  $lookup: {
    'from': 'courses', 
    'localField': 'course1', 'course2', 'course3', 'course4' 
    'foreignField': '_id', 
    'as': 'studentCourses'
  }
}

The source collection in this case says students and the target collection is courses. The number of courses in the source table can be a min of 1 and max of 4. Any of these can be related to a course in course collection.

How can I achieve that?

Answer 1

The localField can only support the value of the string type, which indicates the field name.

While you are looking for $lookup with pipeline, by setting those course field into an courses array, and perform the filtering with $in operator in the lookup pipeline.

db.students.aggregate([
  {
    $lookup: {
      "from": "courses",
      "let": {
        courses: [
          "$course1",
          "$course2",
          "$course3",
          "$course4"
        ]
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$courses"
              ]
            }
          }
        }
      ],
      "as": "studentCourses"
    }
  }
])

Demo @ Mongo Playground

---

Opinion:

Thinking that your Student schema is not a good design. You may think how about the students may take a different number of courses. Possible that the referred `course` field may not exist. Or you need to modify your query again if the `course` field is added/removed each time.

You shall consider a course array field to store those course<X> ids.

---

You may refer to below query below, instead of hardcode the field name, we get the value with the field that contains the prefix: "course".

db.students.aggregate([
  {
    $lookup: {
      "from": "courses",
      "let": {
        courses: {
          $reduce: {
            input: {
              $filter: {
                input: {
                  $objectToArray: "$$ROOT"
                },
                cond: {
                  "$regexMatch": {
                    "input": "$$this.k",
                    "regex": "^course[\\d]$"
                  }
                }
              }
            },
            initialValue: [],
            in: {
              $concatArrays: [
                "$$value",
                [
                  "$$this.v"
                ]
              ]
            }
          }
        }
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$courses"
              ]
            }
          }
        }
      ],
      "as": "studentCourses"
    }
  }
])

Demo 2 @ Mongo Playground