Multiple collection join with inner nested join

Working with Data
,
1

Student Collection

{
  "_id": {
    "$oid": "6390fd9ddb26129a8832e330"
  },
  "name": "Sabarirajan",
  "username": "testuser",
  "schoolId": "639092e21a1df07c4664eb46",
  "grade": 7,
  "type": "user",
  "email": "sabari@thecocoon.in",
  "email1": "sabari1@thecocoon.in",
  "email2": "sabari1@thecocoon.in",
  "mobile": {
    "$numberLong": "9840204606"
  },
  "mobile1": {
    "$numberLong": "9840204606"
  },
  "section": "ab",
  "birthdate": "2022-01-01",
  "uid": "5d04e5d6-e533-462d-bf26-4fac7d1982c4",
  "createdOn": "b7665281-c34a-4b9c-a0db-764d89e600f5",
  "projectDetails": [
    {
      "projectId": {
        "$oid": "6370d25ad4baaabdef7df774"
      },
      "groupId": {
        "$oid": "636f2ae52fe088dd1654e3be"
      }
    },
    {
      "projectId": {
        "$oid": "6370d27cd4baaabdef7df775"
      },
      "groupId": {
        "$oid": "636f2bc32fe088dd1654e3c0"
      }
    }
  ]
}

Project Collection

{
  "_id": {
    "$oid": "6370d25ad4baaabdef7df774"
  },
  "name": "Project-1",
  "id": 1,
  "modifiedBy": "b7665281-c34a-4b9c-a0db-764d89e600f5",
  "modifiedOn": {
    "$date": {
      "$numberLong": "1670753074882"
    }
  },
  "title": "Grade -1 (test)",
  "displayName": "Grade -1 (test)",
  "type": "okjlkj",
  "groups": [
    {
      "$oid": "636f2ae52fe088dd1654e3be"
    },
    {
      "$oid": "636f2bc32fe088dd1654e3c1"
    }
  ]
}

Group Collection

[{
  "_id": {
    "$oid": "636f2ae52fe088dd1654e3be"
  },
  "id": 1,
  "name": "Group-1"
},{
  "_id": {
    "$oid": "636f2bc32fe088dd1654e3c0"
  },
  "id": 2,
  "name": "Group-2"
}]

i have 3 collection student,project,group

notes:
One project have multiple groups.
One student have multiple projects but for one project he have only one group.

i expecting output as following

{
studentdetail…,
projectDetails:[
project:{
projectName:“project-1”,
group:{groupName:“group-1”}
}
project:{
projectName:“project-2”,
group:{groupName:“group-2”}
}
]
}

2

Thanks for sharing sample documents that we can cut-n-paste.

Could you please share what you have tried and explain to us how it fails to provide the expected results. This would save us a lot of time as we won’t spend time investigating in a direction that you already know is wrong. Sometimes we can just point at a small detail that you have wrong.

Having field names email, email1, email2, mobile and mobile1 is a bad schema. Arrays exist for a reason. The attribute pattern exists for a reason. Field names like this are reminiscence of old SQL when arrays were invented decades ago but not possible in early SQL.

Another schema no-no, is date as strings. Dates as Date uses less space, are faster and provides a rich API.

2 Likes
3

@steevej , I am very new to MongoDB and I will change my schema as email and mobile array. and I will save date as Date. thank you very much for your advise that really helps.

I tried something like this (i got projects and group array separately) but i need only one group details inside a project json , groupId that exists in “projectdetails” array

[
  {
    '$match': {
      '_id': new ObjectId('6390fd9ddb26129a8832e330')
    }
  }, {
    '$lookup': {
      'from': 'project', 
      'localField': 'projectDetails.projectId', 
      'foreignField': '_id', 
      'as': 'projects'
    }
  }, {
    '$lookup': {
      'from': 'group', 
      'localField': 'projectDetails.groupId', 
      'foreignField': '_id', 
      'as': 'groups'
    }
  }, {
    '$project': {
      'projects.groups': 0
    }
  }
]

Output:

{
  "_id": {
    "$oid": "6390fd9ddb26129a8832e330"
  },
  "name": "Student Name",
  "uid": "5d04e5d6-e533-462d-bf26-4fac7d1982c4",
  "projects": [
    {
      "_id": {
        "$oid": "6370d27cd4baaabdef7df775"
      },
      "name": "Project-2"
        }
    },
    {
      "_id": {
        "$oid": "6370d25ad4baaabdef7df774"
      },
      "name": "Project-1",
      }
  ],
  "groups": [
    {
      "_id": {
        "$oid": "636f2bc32fe088dd1654e3c0"
      },
      "id": 2,
      "name": "Group-2"
    },
    {
      "_id": {
        "$oid": "636f2ae52fe088dd1654e3be"
      },
      "id": 1,
      "name": "Group-1"
    }
  ]
}

I have project assignment details inside student collection like below. using i need to get projects and its group

"projectDetails": [
    {
      "projectId": {
        "$oid": "6370d25ad4baaabdef7df774"
      },
      "groupId": {
        "$oid": "636f2ae52fe088dd1654e3be"
      }
    }
]

I need output as

student data…

{
//student details  and projectdetails with projects and its single group details inside project json
  "projects": [
    {
      "projectId": {
        "$oid": "6370d25ad4baaabdef7df774"
      },
      "name": "project-1",
      "group": {
        "_id": {
          "$oid": "636f2ae52fe088dd1654e3be"
        },
        "name": "group-1"
      }
    }
  ]
}
4

I do not understand.

In collection students, the array projectDetails has 2 entries with each a projectId and groupId. In the expected result you only have project with name:project-1.

How do you determine which project from the 2 projectDetails you want?