mongodb $lookup with different localfield

Question

I have three different collections. Examples below.

Collection 1
{
    _id: ObjectId('123'),
    studentnumber: 123,
    current_position: 'A',
    schoolId: ObjectId('387')
}

Collection 2
{
    _id: ObjectId('456'),
    studentId: ObjectId('123'),
    studentnumber: 123,
    firstname: 'John',
    firstname: 'Doe',
    schoolId: ObjectId('543')
}

Collection 3
{
    _id: ObjectId('387'),
    schoolName: 'Some school'
},
{
    _id: ObjectId('543'),
    schoolName: 'Some other school'
},

I already have an aggregation query that looks something like this. I am completely new to MongoDB aggregation. I want to know if there is any way to use a field from a different collection in localField of $lookup.

db.collection1.aggregate([
    ///
    $lookup: {
      from: "collection2",
      localField: "studentnumber",
      foreignField: "studentnumber",
      as: "studentnumber",
    },
    ///
    $lookup: {
      from: "collection3",
      localField: "schoolId",
      foreignField: "_id",
      as: "schoolId",
    }
///
])

How can I use the schoolId from collection2 in second $lookup in localField

Current output:

{
    _id: ObjectId('123'),
    firstname: 'John',
    firstname: 'Doe',
    current_position: 'A',
    school: {
        _id: ObjectId('387'),
        schoolName: 'Some school'
    }
}

Expected output:

{
    _id: ObjectId('123'),
    firstname: 'John',
    firstname: 'Doe',
    current_position: 'A',
    school: {
        _id: ObjectId('543'),
        schoolName: 'Some other school'
    }
}

Edit: Updated the current and expected ouput. Added input document.

Charchit Kapoor · Answer

You can try the pipelined form of $lookup. Like this:

db.c1.aggregate([
  {
    "$lookup": {
      "from": "c2",
      "let": {
        id: "$studentnumber"
      },
      "pipeline": [
        {
          "$lookup": {
            "from": "c3",
            "localField": "schoolId",
            "foreignField": "_id",
            "as": "school"
          }
        },
        {
          "$match": {
            "$expr": {
              "$eq": [
                "$$id",
                "$studentId"
              ]
            }
          }
        }
      ],
      "as": "doc"
    }
  },
  {
    "$addFields": {
      "doc": {
        "$arrayElemAt": [
          "$doc",
          0
        ]
      }
    }
  },
  {
    "$addFields": {
      "doc.school": {
        "$arrayElemAt": [
          "$doc.school",
          0
        ]
      }
    }
  },
  {
    "$project": {
      "_id": 1,
      "firstname": 1,
      "lastname": 1,
      "current_position": 1,
      "school": "$doc.school"
    }
  }
])

Check the output here on mongodb playground.

mongodb $lookup with different localfield

1 Answers