MongoDB $lookup聚合查询优化：获取全科目关联主题及子项数据

需求描述 #

需关联三张表实现以下查询：

• 根据stageid和boardid从科目表获取所有符合条件的科目；
• 从edcontentmaster表获取每个科目下的所有主题及内容详情，并按topicid分组；
• 每个主题需包含从edchildrevisioncompleteschemas表获取的对应子项详情。

当前查询代码 #

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,
})

当前输出 #

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

期望输出 #

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

问题说明 #

当前查询仅能获取单个科目的数据，需修改以获取所有符合条件的科目，且每个科目包含对应的主题及子项数据结构。

修改后的查询代码 #

const { stageid, boardid, scholarshipid, childid } = req.params;
// 假设科目表名为edSubjects，请根据实际表名调整
edSubjects.aggregate([
  // 1. 筛选符合条件的所有科目
  {
    $match: {
      "stageid": stageid,
      "boardid": boardid,
      "scholarshipid": scholarshipid // 按需保留或删除
    }
  },
  // 2. 关联edcontentmaster，获取该科目下的所有主题内容
  {
    $lookup: {
      from: "edcontentmaster",
      let: { subjectid: "$subjectid", stageid: "$stageid", boardid: "$boardid" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$subjectid", "$$subjectid"] },
                { $eq: ["$stageid", "$$stageid"] },
                { $eq: ["$boardid", "$$boardid"] }
              ]
            }
          }
        },
        {
          $addFields: {
            "convertedField": {
              $cond: {
                if: { $eq: ["$slcontent", ""] },
                then: "$slcontent",
                else: { $toInt: "$slcontent" }
              }
            }
          }
        },
        { $sort: { "slcontent": 1 } },
        // 3. 关联edchildrevisioncompleteschemas，获取主题对应的子项详情
        {
          $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"
          }
        },
        // 4. 按topic分组，聚合主题下的内容集合
        {
          $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,
            reviewquestionsets: 1
          }
        },
        { $sort: { "sltopic": 1 } }
      ],
      as: "topicDetails"
    }
  },
  // 5. 对科目下的主题列表按sltopic排序
  {
    $addFields: {
      topicDetails: {
        $sortArray: {
          input: "$topicDetails",
          sortBy: { sltopic: 1 }
        }
      }
    }
  }
]).collation({
  locale: "en_US",
  numericOrdering: true
})

修改说明 #

1. 切换查询起点：从科目表开始查询，一次性获取所有符合stageid和boardid条件的科目，避免单科目限制。
2. 嵌套关联逻辑：在科目表的$lookup中嵌套原有的edcontentmaster处理流程，包括字段转换、排序、关联子项表及主题分组，确保每个科目下的主题结构符合要求。
3. 移除单科目过滤：删除原代码中$match里的subjectid条件，不再限定单个科目。
4. 主题排序优化：添加$sortArray确保每个科目下的主题按sltopic数字顺序排列，符合输出要求。

内容的提问来源于stack exchange，提问作者Rajesh Senapati