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
You should join the subject collection with the edcontentmaster via
$lookup
. Thepipeline
in the$lookup
stage should be your existing query.In the last stage, convert the
topicDetails
to an object by getting the first element.Demo @ Mongo Playground