//issues-collection
[
  {
     project: ObjectId("67"), // ref to a project
     id: 101,
     assignedTo: ObjectId("1212") // ref to a user
  }
]


//project-collection
[
  {
     _id: ObjectId("67"),
     title: "Proj 1",
     members: [
       {
          _id: ObjectId("213213"),
          user: ObjectId("1212"), // ref to a user
          discipline: "Architect"
       }
     ]  
  }
]

I need a query for the issue collection on the disciplin field from the project collection

The $lookup should be used. Something like this.

const getIssuesByDiscipline = (projectId, discpline: string) => {
   const res = mongoose.model("issues").aggregate([
     {
      $lookup: {
        from: "project",
        localField: "assignedTo",
        foreignField: "members.user",
        as: "project",
        //pipeline: ?
     }
   ]
},

}...

Not sure how to use $lookup when the foregin field is an array. Maybe something with pipeline?

Any suggestion?

CodePudding user response：

I think you want something like:

MongoDB version 5.0 or higher:

1. Use the project from issues collection to find the documents on project collection with the matching _id
2. Use the pipeline to $filter relevant users

db.issues.aggregate([
  {$lookup: {
      from: "project",
      localField: "project",
      foreignField: "_id",
      as: "project",
      let: {assignedTo: "$assignedTo"},
      pipeline: [
        {$project: {
            members: {$filter: {
                input: "$members",
                cond: {$eq: ["$$this.user", "$$assignedTo"]}
            }}
        }}
      ]
  }}
])

See how it works on the playground example

MongoDB versions before 5.0: Can't use both localField, foreignField with a pipeline. Instead, add another phase to the pipeline (as a first phase) to match the relevant documents.