MongoDB查找（JOIN），带有双嵌套数组中的字段

Question

使用MongoDB集合名称部门带有以下结构：

{
  "_id":99,
  "name":"Erick Kalewe",
  "faculty":"Zazio",
  "lecturers":[
    {
      "lecturerID":31,
      "name":"Granny Kinton",
      "email":"[email protected]",
      "imparts":[
        {
          "groupID":70,
          "codCourse":99
        }
      ]
    },
    {
      "lecturerID":36,
      "name":"Michale Dahmel",
      "email":"[email protected]",
      "imparts":[
        {
          "groupID":100,
          "codCourse":60
        }
      ]
    }
  ]
}

另一个集合组带有此结构：

{
  "_id":100,
  "codCourse":11,
  "language":"Romanian",
  "max_students":196,
  "students":[
    {
      "studentID":1
    }
  ],
  "classes":[
    {
      "date":datetime.datetime(2022, 5, 10, 4, 24, 19),
      "cod_classroom":100
    }
  ]
}

加入它们以获取以下内容：

{
  "_id":99,
  "name":"Erick Kalewe",
  "faculty":"Zazio",
  "lecturers":[
    {
      "lecturerID":31,
      "name":"Granny Kinton",
      "email":"[email protected]",
      "imparts":[
        {
          "groupID":70,
          "codCourse":99
        }
      ]
    },
    {
      "lecturerID":36,
      "name":"Michale Dahmel",
      "email":"[email protected]",
      "imparts":[
        {
          "_id":100,
          "codCourse":11,
          "language":"Romanian",
          "max_students":196,
          "students":[
            {
              "studentID":1
            }
          ],
          "classes":[
            {
              "date":datetime.datetime(2022, 5, 10, 4, 24, 19),
              "cod_classroom":100
            }
          ]
        }
      ]
    }
  ]
}

目标是获取带有号码的报告由一位教授教授的学生。

Answer 1

查询

• 放松一下，进行加入，然后重新组备份
• 其大查询，因为您想加入嵌套领域，这意味着2个放松和2个分组以还原结构
  （我认为总体上加入字段不应该深入内部）
• 放松两个阵列
• 在GroupID上进行查找
• ，现在将文档构造为2级，
• 首先嵌套了它的影响，其影响需要分组和推开
  （对于休息参数，我先保留$）
  我们还根据评论将学生总结，
• 然后是我需要分组并推动的讲师
  （对于休息论点，我先保留$）
  我们与该系的最高学生一起演讲
  （MongoDB也可以比较文档）

IN/OUT ）

department.aggregate(
[{"$unwind": "$lecturers"}, {"$unwind": "$lecturers.imparts"},
 {"$lookup": 
   {"from": "coll",
    "localField": "lecturers.imparts.groupID",
    "foreignField": "_id",
    "as": "lecturers.imparts"}},
 {"$set": {"lecturers.imparts": {"$first": "$lecturers.imparts"}}},
 {"$group": 
   {"_id": {"_id": "$_id", "lecturersID": "$lecturers.lecturerID"},
    "name": {"$first": "$name"},
    "faculty": {"$first": "$faculty"},
    "lecturers": 
     {"$first": 
       {"lecturerID": "$lecturers.lecturerID",
        "name": "$lecturers.name",
        "email": "$lecturers.email"}},
    "imparts": {"$push": "$lecturers.imparts"},
    "lecture_max_students": 
     {"$sum": "$lecturers.imparts.max_students"}}},
 {"$set": 
   {"lecturers": 
     {"$mergeObjects": 
       ["$lecturers", {"imparts": "$imparts"},
         {"lecture_max_students": "$lecture_max_students"}]},
    "imparts": "$REMOVE","lecture_max_students": "$REMOVE"}},
 {"$group": 
   {"_id": "$_id._id",
    "name": {"$first": "$name"},
    "faculty": {"$first": "$faculty"},
    "lectures": {"$push": "$lecturers"},
    "dept-max-lecturer": 
     {"$max": {"max-students": "$lecturers.lecture_max_students",
               "lecturerID": "$lecturers.lecturerID"}}}}])

Answer 2

现在我们已经了解了一个问题（根据您的其他问题），答案可以是） ：

1. 添加每个部门记录其所有相关组的集合。
2. $查找仅为每个组创建 groups 数组的学生ID。
3. 将相关的组数据插入每个讲师。
4. 的唯一学生
5. 计算 maximpartsStudents ，这是来自其所有 groups groups $ ydard $ redion 讲师数组仅包括讲师带有最高 maximpartsstudents 。
6. 格式化的答案

db.department.aggregate([
  {
    $addFields: {
      groups: {
        $setIntersection: [
          {
            $reduce: {
              input: "$lecturers.imparts.groupID",
              initialValue: [],
              in: {$concatArrays: ["$value", "$this"]}
            }
          }
        ]
      }
    }
  },
  {
    $lookup: {
      from: "group",
      let: {groupIDs: "$groups"},
      pipeline: [
        {$match: {$expr: {$in: ["$_id", "$groupIDs"]}}},
        {
          $project: {
            students: {
              $reduce: {
                input: "$students",
                initialValue: [],
                in: {$concatArrays: ["$value", ["$this.studentID"]]}
              }
            }
          }
        }
      ],
      as: "groups"
    }
  },
  {
    $project: {
      name: 1,
      lecturers: {
        $map: {
          input: "$lecturers",
          in: {
            $mergeObjects: [
              {lecturerID: "$this.lecturerID"},
              {groups: {
                  $map: {
                    input: "$this.imparts",
                    in: {
                      $arrayElemAt: [
                        "$groups",
                        {$indexOfArray: ["$groups._id", "$this.groupID"]}
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      name: 1,
      lecturers: {
        $map: {
          input: "$lecturers",
          as: "item",
          in: {
            $mergeObjects: [
              {
                maxImpartsStudents: {
                  $size: {
                    $reduce: {
                      input: "$item.groups",
                      initialValue: [],
                      in: {$setUnion: ["$value", "$this.students"]}
                    }
                  }
                }
              },
              {lecturerID: "$item.lecturerID"}
            ]
          }
        }
      }
    }
  },
  {
    $set: {
      lecturers: {
        $reduce: {
          input: "$lecturers",
          initialValue: {
            "maxImpartsStudents": 0
          },
          in: {
            $cond: [
              {$gte: ["$this.maxImpartsStudents", "$value.maxImpartsStudents"]},
              "$this", "$value"
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      lecturerID: "$lecturers.lecturerID",
      maxImpartsStudents: "$lecturers.maxImpartsStudents",
      departmentName: "$name"
    }
  }
])

比结合两个问题的解决方案要好得多。

请查看其在

Answer 3

You can try aggregation framework,

• $lookup with group collection pass lecturers.imparts.groupID as localField and pass <代码> _id AS foreffield
• $ addfields to merge group group imports 和remove group 字段，因为不需要
• $ map 要迭代讲师 array> array
• $ mergeObject 以合并的当前对象讲师和 imports $ map 的更新对象
• ，要迭代 imports array array
• $ mergeObjects 合并的当前对象导入，并从 group
• $ filter 找到 group> group array的循环并通过 groupId
• $ arrayElemat 从上面过滤结果获取第一个元素

db.department.aggregate([
  {
    $lookup: {
      from: "group",
      localField: "lecturers.imparts.groupID",
      foreignField: "_id",
      as: "group"
    }
  },
  {
    $addFields: {
      lecturers: {
        $map: {
          input: "$lecturers",
          in: {
            $mergeObjects: [
              "$this",
              {
                imparts: {
                  $map: {
                    input: "$this.imparts",
                    as: "i",
                    in: {
                      $mergeObjects: [
                        "$i",
                        {
                          $arrayElemAt: [
                            {
                              $filter: {
                                input: "$group",
                                cond: { $eq: ["$this._id", "$i.groupID"] }
                              }
                            },
                            0
                          ]
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      },
      group: "$REMOVE"
    }
  }
])