How to populate in MongoDB without mongoose?-CodePudding

I'm working on a university project and I need to populate an array of objects with ObjectIds, but I can't use mongoose in my project. I have two collections - subject and studyProgram.

Example studyProgram document:

{
  _id: ObjectId('111'),
  name: "Study program 1"
  description: "Lorem ipsum dolor sit amet",
  language: "en",
  subjects: [
    {
      id: ObjectId('222'),
      optionality: "selective",
      credits: 8,
    },
    {
      id: ObjectId('333'),
      optionality: "selective",
      credits: 5
    },
  ],
}

Example subject documents:

{
  _id: ObjectId('222'),
  name: "Subject A",
  description: "Subject A description.",
},
{
  _id: ObjectId('333'),
  name: "Subject B",
  description: "Subject B description.",
}

I need to populate objects in subjects array with appropriate documents from subject collection based on id. Basically what I'm looking for is this result:

{
  _id: ObjectId('111'),
  name: "Study program 1"
  description: "Lorem ipsum dolor sit amet",
  language: "en",
  subjects: [
    {
      
      _id: ObjectId('222'),
      name: "Subject A",
      description: "Subject A description.",
      optionality: "selective",
      credits: 8,
    },
    {
      _id: ObjectId('333'),
      name: "Subject B",
      description: "Subject B description.",
      optionality: "selective",
      credits: 5
    },
  ],
}

So far I have tried using the following $lookup:

{
  $lookup: {
    from: "subject",
    localField: "subjects.id",
    foreignField: "_id",
    as: "subjects",
  }
}

but this removes the optionality and credits attributes. Is there a way to achieve this without having to use mongoose? Thank you.

CodePudding user response：

Here's one way to do it.

db.studyProgram.aggregate([
  {
    "$lookup": {
      "from": "subject",
      "localField": "subjects.id",
      "foreignField": "_id",
      "as": "subjectDocs"
    }
  },
  {
    "$set": {
      "subjects": {
        "$map": {
          "input": "$subjects",
          "as": "subject",
          "in": {
            "$mergeObjects": [
              {
                "$first": {
                  "$filter": {
                    "input": "$subjectDocs",
                    "as": "doc",
                    "cond": {"$eq": ["$$doc._id", "$$subject.id"]}
                  }
                }
              },
              {
                "optionality": "$$subject.optionality",
                "credits": "$$subject.credits"
              }
            ]
          }
        }
      },
      "subjectDocs": "$$REMOVE"
    }
  }
])

Try it on mongoplayground.net.

If "$first" is unavailable, tell your SysAdmin to upgrade MongoDB! Until then, this will probably work.

db.studyProgram.aggregate([
  {
    "$lookup": {
      "from": "subject",
      "localField": "subjects.id",
      "foreignField": "_id",
      "as": "subjectDocs"
    }
  },
  {
    "$set": {
      "subjects": {
        "$map": {
          "input": "$subjects",
          "as": "subject",
          "in": {
            "$mergeObjects": [
              {
                "$arrayElemAt": [
                  {
                    "$filter": {
                      "input": "$subjectDocs",
                      "as": "doc",
                      "cond": {"$eq": ["$$doc._id", "$$subject.id"]}
                    }
                  },
                  0
                ]
              },
              {
                "optionality": "$$subject.optionality",
                "credits": "$$subject.credits"
              }
            ]
          }
        }
      },
      "subjectDocs": "$$REMOVE"
    }
  }
])

Try it on mongoplayground.net.