Join return no result with ObjectId and a non ObjectId

Question

I have collection expertKstag

{
    _id: ObjectId('6244213ec4c8aa000104d5ba'),
    userID: '60a65e6142e3320001cc8178',
    uid: 'klavidal',
    firstName: 'Kevin',
    name: 'Lavidal',
    email: 'kevin.lavidal@xxx.fr',
    expertProfileInProgressList: {},
    expertProfileList: [
        {
            _id: ObjectId('6453abc94e5cd20001596e1c'),
            version: 0,
            language: 'fr',
            isReference: true,
            state: 'PUBLISHED',
            personalDetails: {
            firstName: 'Kevin',
            name: 'Lavidal',
            email: 'kevin.lavidal@xxx.fr',
                isAbesIDFromLdap: false,
                requiredFieldsLeft: false
            },
            professionalStatus: {
                corpsID: '62442223b8fb982305a5bd67',
                lastUpdateDate: ISODate('2023-05-05T08:36:51.327Z')
            }
    ],
    _class: 'fr.ubordeaux.thehub.expertprofilesservice.model.dao.indexed.ExpertIndexed'
}

and collection nomenclatureKstag

{
    _id: ObjectId('62442223b8fb982305a5bd67'),
    type: 'STATUT_CORPS',
    level: 1,
    hasCNU: true,
    labels: [
        {
            language: 'fr',
            text: 'Enseignant-chercheur'
        },
        {
            language: 'en',
            text: 'Teacher-Researcher'
        }
    ],
    isValid: true
}

I wanna join expertKstag -> expertProfileList.professionalStatus.corpsID and nomenclatureKstag -> _id

I try but nothing is returned, why?

db.expertKstag.aggregate([
   {
      $lookup:
         {
           from: "nomenclatureKstag",
           localField: "expertKstag.expertProfileList.professionalStatus.corpsID",
           foreignField: "_id",
           as: "joinresultat"
         }
   },
   {
      $unwind: "$join_resultat"
   },
   {
      $project: {
         "_id": 1,
         "userID": 1,
         "uid": 1,
         "firstName": 1,
         "name": 1,
         "email": 1,
         "join_resultat.isValid": 1
      }
   }
])

Thank you for helping, I think the problem is the join _id is type ObjectId and expertKstag.expertProfileList.professionalStatus.corpsID is not.

Answer 1

There are a few errors in your query:

1. The expertKstag.expertProfileList.professionalStatus.corpsID field doesn't exist. You mean expertProfileList.professionalStatus.corpsID from the expertKstag collection.

2. To compare/match the values, both values should be of the same type in order to match correctly.

3. The result after the $lookup stage will create a new field name joinresultat in the array. But the $unwind stage refers to this join_resultat field, which never exists. Thus the result output is empty.

Your query should be as below:

db.expertKstag.aggregate([
  {
    $lookup: {
      from: "nomenclatureKstag",
      let: {
        corpsIDs: {
          $map: {
            input: "$expertProfileList.professionalStatus.corpsID",
            in: {
              $toObjectId: "$$this"
            }
          }
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$corpsIDs"
              ]
            }
          }
        }
      ],
      as: "join_resultat"
    }
  },
  {
    $unwind: "$join_resultat"
  },
  {
    $project: {
      "_id": 1,
      "userID": 1,
      "uid": 1,
      "firstName": 1,
      "name": 1,
      "email": 1,
      "join_resultat.isValid": 1
    }
  }
])

Demo @ Mongo Playground