skip to Main Content

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": ""
}
}
]

2

Answers


  1. Chosen as BEST ANSWER
    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" }, 
      },
    },
    ])
    

  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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search