MongoDB多集合查询问题



  • 从MongoDB 3.2版开始,支持使用$lookup()进行多集合联接查询

    假设ClassMembers集合的文档结构类似如下文档:

    {
       "ClassID":"CL0001";
       "StudentID":"S000001";
       "DateFrom":"2015-09-01";
       "DateTo":null
    }
    {
       "ClassID":"CL0002";
       "StudentID":"S000002";
       "DateFrom":"2015-09-01";
       "DateTo":null
    }

    Scores集合的文档结构类似如下文档:

    {
       "StudentID":"S000001";
       "TestID":"T000001";
        "Scores:":[{"Course":"Chinese","Score":80},{"Course":"Maths","Score":70}{"Course":"English","Score":75}]
    }
    {
       "StudentID":"S000002";
       "TestID":"T000001";
        "Scores:":[{"Course":"Chinese","Score":70},{"Course":"Maths","Score":80}{"Course":"English","Score":82}]
    }

    如何查询ClassID为CL0001,TestID为T000001的全部学生的Chinese的成绩?

  • Lv 1

    @x4_965be


    db.ClassMembers.insertMany([
        {
           "ClassID":"CL0001",
           "StudentID":"S000001",
           "DateFrom":"2015-09-01"
        },
        {
           "ClassID":"CL0002",
           "StudentID":"S000002",
           "DateFrom":"2015-09-01"
        }
    ]); db.Scores.insertMany([
    {
       "StudentID":"S000001",
       "TestID":"T000001",
        "Scores":[{
            "Course":"Chinese",
            "Score":80
        },{
            "Course":"Maths",
            "Score":70
        },{
            "Course":"English",
            "Score":75
        }]
    },
    {
       "StudentID":"S000002",
       "TestID":"T000001",
        "Scores":[{
            "Course":"Chinese",
            "Score":70
        },{
            "Course":"Maths",
            "Score":80
        },{
            "Course":"English",
            "Score":82
        }]
    }
    ]); db.ClassMembers.aggregate([
        {
            $match: {ClassID: 'CL0001'}
        },
        {
          $lookup:
            {
              from: "Scores",
              localField: "StudentID",
              foreignField: "StudentID",
              as: "ScoresDocs"
            }
       },
       { $match : { "ScoresDocs.TestID" : 'T000001' }},
       { $project: { _id: 0, ClassID: 1, ScoresDocs: 1}},
       { $unwind: '$ScoresDocs'},
       { $unwind: '$ScoresDocs.Scores'},
       { $match: {'ScoresDocs.Scores.Course':'Chinese'}}
          
    ])

登录后回复
 

与 萌阔论坛 的连接断开,我们正在尝试重连,请耐心等待