MongoDB - $lookup not getting an appropriate result

Question

Here I just need to look up three tables first need to get all subjects from the subject table according to classid and boardid then from the content table we need to get all topic and content details and group them by topicid(please look into expecting output). then each topic details should contain child details which we will get from edchildrevisioncompleteschemas table #lookup code

const { stageid, subjectid, boardid, scholarshipid, childid } = req.params;
edcontentmaster
.aggregate([
  {
    $match: {
      stageid: stageid,
      subjectid: subjectid,
      boardid: boardid,
      // scholarshipid: scholarshipid,
    },
  },
  {
    $addFields: {
      convertedField: {
        $cond: {
          if: { $eq: ["$slcontent", ""] },
          then: "$slcontent",
          else: { $toInt: "$slcontent" },
        },
      },
    },
  },
  {
    $sort: {
      slcontent: 1,
    },
  },
  {
    $lookup: {
      from: "edchildrevisioncompleteschemas",
      let: { childid: childid, subjectid:subjectid,topicid:"$topicid" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$childid",
                    "$$childid"
                  ]
                },
                {
                  $in: [
                    "$$subjectid",
                    "$subjectDetails.subjectid"
                  ]
                },
                {
                  $in: [
                    "$$topicid",
                    {
                      $reduce: {
                        input: "$subjectDetails",
                        initialValue: [],
                        in: {
                          $concatArrays: [
                            "$$value",
                            "$$this.topicDetails.topicid"
                          ]
                        }
                      }
                    }
                  ]
                }
              ]
            }
          }
        },
        {
          $project: {
            _id: 1,
            childid: 1
          }
        }
      ],
      as: "studenttopic",
    },
  },
  {
    $group: {
      _id: "$topic",
      topicimage: { $first: "$topicimage" },
      topicid: { $first: "$topicid" },
      sltopic: { $first: "$sltopic" },
      studenttopic: { $first: "$studenttopic" },
      reviewquestionsets: {
        $push: {
          id: "$_id",
          sub: "$sub",
          topic: "$topic",
          contentset: "$contentset",
          stage: "$stage",
          timeDuration: "$timeDuration",
          contentid: "$contentid",
          studentdata: "$studentdata",
          subjectIamge: "$subjectIamge",
          topicImage: "$topicImage",
          contentImage: "$contentImage",
          isPremium: "$isPremium",
        },
      },
    },
  },
  {
    $project: {
      _id: 0,
      topic: "$_id",
      topicimage: 1,
      topicid: 1,
      sltopic: 1,
      studenttopic:1,
      contentid: "$contentid",
      reviewquestionsets: 1,
    },
  },
])
.sort({ sltopic: 1 })
.collation({
  locale: "en_US",
  numericOrdering: true,
})

from the above query I am getting appropriate data for a single subject, but I need all subjects from subject table and each subject should have the same format data that i getting for a single subject, ex-mongoplayground.net/p/LoxSBI3jZL-

current output-

[
{
"reviewquestionsets": [
  {
    "contentid": "NVOOKADA1690811843420STD-5EnglishThe Monkey 
from RigerLesson - 1",
    "contentset": "Lesson - 1",
    "id": ObjectId("64ccd53792362c7639d3da5f"),
    "stage": "STD-5",
    "timeDuration": "15",
    "topic": "The Monkey from Riger"
  },
  {
    "contentid": "NVOOKADA1690811843420STD-5EnglishThe Monkey 
 from RigerLesson - 3",
    "contentset": "Lesson - 3",
    "id": ObjectId("64ccf5ca92362c7639d3f145"),
    "isPremium": true,
    "stage": "STD-5",
    "timeDuration": "5",
    "topic": "The Monkey from Riger"
  }
],
"sltopic": "1",
"studenttopic": [
  {
    "_id": ObjectId("659580293aaddf7594689d18"),
    "childid": "WELL1703316202984"
  }
],
"topic": "The Monkey from Riger",
"topicid": "1691144002706",
"topicimage": ""
}
]

expected output-

[
{
"_id": "64cc9a2656738e9f1507f521",
"subjectid": "1691130406151",
"subject": "English",
"subjectImage": "https://wkresources.s3.ap-south- 
1.amazonaws.com/1691761437925_644750345.png",
"stageid": "5",
"stage": "STD-5",
"boardid": "1",
"boardname": "BSE",
"scholarshipid": "NVOOKADA1690811843420",
"scholarshipname": "Adarsh",
"createon": "2023-08-04T06:26:46.154Z",
"updatedon": "2023-08-14T13:07:16.256Z",
"__v": 0,
"slsubject": "1",
"topicDetails": {
  "reviewquestionsets": [
    {
      "contentid": "NVOOKADA1690811843420STD-5EnglishThe Monkey 
from RigerLesson - 1",
      "contentset": "Lesson - 1",
      "id": "64ccd53792362c7639d3da5f",
      "stage": "STD-5",
      "timeDuration": "15",
      "topic": "The Monkey from Riger"
    },
    {
      "contentid": "NVOOKADA1690811843420STD-5EnglishThe Monkey 
from RigerLesson - 3",
      "contentset": "Lesson - 3",
      "id": "64ccf5ca92362c7639d3f145",
      "isPremium": true,
      "stage": "STD-5",
      "timeDuration": "5",
      "topic": "The Monkey from Riger"
    }
  ],
  "sltopic": "1",
  "studenttopic": [
    {
      "_id": "659580293aaddf7594689d18",
      "childid": "WELL1703316202984"
    }
  ],
  "topic": "The Monkey from Riger",
  "topicid": "1691144002706",
  "topicimage": ""
}
}
]

Answer 1

edSubject.aggregate([
{
  $match: {
    stageid: stageid,
    boardid: boardid,
    scholarshipid: scholarshipid,
  },
},
{
  $lookup: {
    from: "edcontentmasterschemas",
    let: {
      stageid: "$stageid",
      subjectid: "$subjectid",
      boardid: "$boardid",
      scholarshipid: "$scholarshipid",
    },
    pipeline: [
      {
        $match: {
          $expr: {
            $and: [
              { $eq: ["$stageid", "$$stageid"] },
              { $eq: ["$subjectid", "$$subjectid"] },
              { $eq: ["$boardid", "$$boardid"] },
              { $eq: ["$scholarshipid", "$$scholarshipid"] },
            ],
          },
        },
      },
      {
        $addFields: {
          convertedField: {
            $cond: {
              if: { $eq: ["$slcontent", ""] },
              then: "$slcontent",
              else: { $toInt: "$slcontent" },
            },
          },
        },
      },
      { $sort: { slcontent: 1 } },
      {
        $group: {
          _id: "$topicid",
          topicimage: { $first: "$topicimage" },
          topic: { $first: "$topic" },
          sltopic: { $first: "$sltopic" },
          studenttopic: { $first: "$studenttopic" },
          reviewquestionsets: {
            $push: {
              id: "$_id",
              sub: "$sub",
              topic: "$topic",
              contentset: "$contentset",
              stage: "$stage",
              timeDuration: "$timeDuration",
              contentid: "$contentid",
              studentdata: "$studentdata",
              subjectIamge: "$subjectIamge",
              topicImage: "$topicImage",
              contentImage: "$contentImage",
              isPremium: "$isPremium",
            },
          },
        },
      },
      {
        $addFields: {
          convertedField: {
            $cond: {
              if: { $eq: ["$slcontent", ""] },
              then: "$slcontent",
              else: { $toInt: "$slcontent" },
            },
          },
        },
      },
      { $sort: { sltopic: 1 } },
      {
        $lookup: {
          from: "edchildrevisioncompleteschemas",
          let: {
            childid: childid,
            //childid,
            //subjectid,
            topicid: "$_id"
          },
          pipeline: [
            {
              $match: {
                $expr: {
                  $and: [
                    {
                      $eq: [
                        "$childid",
                        "$$childid"
                      ]
                    },
                    {
                      $in: [
                        "$$topicid",
                        {
                          $reduce: {
                            input: "$subjectDetails",
                            initialValue: [],
                            in: {
                              $concatArrays: [
                                "$$value",
                                "$$this.topicDetails.topicid"
                              ]
                            }
                          }
                        }
                      ]
                    }
                  ]
                }
              }
            },
            {
              $project: {
                _id: 1,
                childid: 1
              }
            }
          ],
          as: "studenttopic"
        }
      },
      {
        $project: {
          _id: 0,
          topic: "$_id",
          topicimage: 1,
          topicid: 1,
          sltopic: 1,
          studenttopic: 1,
          contentid: "$contentid",
          reviewquestionsets: 1,
        },
      },
    ],
    as: "topicDetails",
  },
},
{ $unwind: "$topicDetails" }, 
{
  $group: {
    _id: "$_id",
    subject: { $first: "$subject" },
    subjectid: { $first: "$subjectid" },
    slsubject: { $first: "$slsubject" },
    topicDetails: { $push: "$topicDetails" }, 
  },
},
])

Answer 2

You should join the subject collection with the edcontentmaster via $lookup. The pipeline in the $lookup stage should be your existing query.

In the last stage, convert the topicDetails to an object by getting the first element.

db.subject.aggregate([
  {
    $match: {
      stageid: "5",
      boardid: "1",
      scholarshipid: "NVOOKADA1690811843420"
    }
  },
  {
    $lookup: {
      from: "edcontentmaster",
      let: {
        stageid: "$stageid",
        subjectid: "$subjectid",
        boardid: "$boardid",
        scholarshipid: "$scholarshipid"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$stageid",
                    "$$stageid"
                  ]
                },
                {
                  $eq: [
                    "$subjectid",
                    "$$subjectid"
                  ]
                },
                {
                  $eq: [
                    "$boardid",
                    "$$boardid"
                  ]
                },
                {
                  $eq: [
                    "$scholarshipid",
                    "$$scholarshipid"
                  ]
                }
              ]
            }
          }
        },
        {
          $addFields: {
            convertedField: {
              $cond: {
                if: {
                  $eq: [
                    "$slcontent",
                    ""
                  ]
                },
                then: "$slcontent",
                else: {
                  $toInt: "$slcontent"
                }
              }
            }
          }
        },
        {
          $sort: {
            slcontent: 1
          }
        },
        {
          $group: {
            _id: "$topic",
            topicimage: {
              $first: "$topicimage"
            },
            topicid: {
              $first: "$topicid"
            },
            sltopic: {
              $first: "$sltopic"
            },
            studenttopic: {
              $first: "$studenttopic"
            },
            reviewquestionsets: {
              $push: {
                id: "$_id",
                sub: "$sub",
                topic: "$topic",
                contentset: "$contentset",
                stage: "$stage",
                timeDuration: "$timeDuration",
                contentid: "$contentid",
                studentdata: "$studentdata",
                subjectIamge: "$subjectIamge",
                topicImage: "$topicImage",
                contentImage: "$contentImage",
                isPremium: "$isPremium"
              }
            }
          }
        },
        {
          $lookup: {
            from: "edchildrevisioncompleteschemas",
            let: {
              childid: "WELL1703316202984",
              //childid,
              subjectid: "1691130406151",
              //subjectid,
              topicid: "$topicid"
            },
            pipeline: [
              {
                $match: {
                  $expr: {
                    $and: [
                      {
                        $eq: [
                          "$childid",
                          "$$childid"
                        ]
                      },
                      {
                        $in: [
                          "$$subjectid",
                          "$subjectDetails.subjectid"
                        ]
                      },
                      {
                        $in: [
                          "$$topicid",
                          {
                            $reduce: {
                              input: "$subjectDetails",
                              initialValue: [],
                              in: {
                                $concatArrays: [
                                  "$$value",
                                  "$$this.topicDetails.topicid"
                                ]
                              }
                            }
                          }
                        ]
                      }
                    ]
                  }
                }
              },
              {
                $project: {
                  _id: 1,
                  childid: 1
                }
              }
            ],
            as: "studenttopic"
          }
        },
        {
          $project: {
            _id: 0,
            topic: "$_id",
            topicimage: 1,
            topicid: 1,
            sltopic: 1,
            studenttopic: 1,
            contentid: "$contentid",
            reviewquestionsets: 1
          }
        }
      ],
      as: "topicDetails"
    }
  },
  {
    $set: {
      topicDetails: {
        $first: "$topicDetails"
      }
    }
  }
])

Demo @ Mongo Playground