Lookup 2 collection

[Nuur_zakki_Zamani]

hello friends, I have a document that contains students and classes (2 collections students and classes).

each student has a mainclassid and classid.
this is an example document and the result of the aggregation that I made.
this code return 20 document from 5 document

Mongo playground

the results shown do not match my expectations,
I hope that every student will have a different classid like the example below

[
      {
      "_id": ObjectId("5a934e000102030405000001"),
      "classId": ObjectId("635d2f796804b95ce6a9e5d1"),
      "email": "some@mail.com",
      "kelas": {
        "classes": {
          "classCode": "VII B",
          "classId": ObjectId("635d2f796804b95ce6a9e5d1"),
          "className": "B"
        },
        "mainClass": "VII"
      },
      "name": "STUDENT 1",
      "phone": "08712345678",
      "regDate": "29/10/2022",
      "tags": []
    },
    
    {
      "_id": ObjectId("5a934e000102030405000002"),
      "classId": ObjectId("635d2f796804b95ce6a9e5d1"),
      "email": "some@mail.com",
      "kelas": {
        "classes": {
          "classCode": "VII C",
          "classId": ObjectId("635d2f836804b95ce6a9e5d2"),
          "className": "C"
        },
        "mainClass": "VII"
      },
      "name": "STUDENT 2",
      "phone": "08712345678",
      "regDate": "29/10/2022",
      "tags": []
    },
    
    {
      "_id": ObjectId("5a934e000102030405000003"),
      "classId": ObjectId("635d2f796804b95ce6a9e5d1"),
      "email": "some@mail.com",
      "kelas": {
        "classes": {
          "classCode": "VII D",
          "classId": ObjectId("635d2f8e6804b95ce6a9e5d3"),
          "className": "D"
        },
        "mainClass": "VII"
      },
      "name": "STUDENT 3",
      "phone": "08712345678",
      "regDate": "29/10/2022",
      "tags": []
    },
    
    {
      "_id": ObjectId("5a934e000102030405000004"),
      "classId": ObjectId("635d2f796804b95ce6a9e5d1"),
      "email": "some@mail.com",
      "kelas": {
        "classes": {
          "classCode": "VII E",
          "classId": ObjectId("635d2f966804b95ce6a9e5d4"),
          "className": "E"
        },
        "mainClass": "VII"
      },
      "name": "STUDENT 4",
      "phone": "08712345678",
      "regDate": "29/10/2022",
      "tags": []
    }
  ]

[Jason_Tran]

Hi @Nuur_zakki_Zamani,

Would the following aggregation get your desired output? Please note: instead of "kelas" from your expected output, the corresponding would be "filteredClasses" in the playground link:

db.users.aggregate([
  {
    "$match": {
      "companyId": ObjectId("635c70892e8cfaf4a7d49a3f")
    }
  },
  {
    "$lookup": {
      "from": "tbl_classes",
      "localField": "mainClassId",
      "foreignField": "_id",
      "as": "kelas"
    }
  },
  {
    "$unwind": "$kelas"
  },
  {
    "$project": {
      "name": 1,
      "classId": 1,
      "email": 1,
      "phone": 1,
      "regDate": {
        "$dateToString": {
          "date": "$regDate",
          "format": "%d/%m/%Y"
        }
      },
      "tags": 1,
      "filteredClasses": {
        "$filter": {
          "input": "$kelas.classes",
          "as": "classes",
          "cond": {
            "$eq": [
              "$classId",
              "$$classes.classId"
            ]
          }
        }
      }
    }
  }
])

The main difference is I had used a $filter to get the matching "classId" values from the "kelas.classes" array.

If you still require further assistance, please let me know which particular fields or certain details are incorrect. Otherwise if you believe this works for you, please alter it accordingly and test thoroughly to ensure it suits all your use case(s) and requirements.

Regards,
Jason

[Nuur_zakki_Zamani]

alhamdulillah… thank you very much bro @Jason_Tran … exactly what I wanted.