如何使用$lookup和$in mongodb聚合

Question

学院

{
    "_id" : ObjectId("5cd42b5c65b41027845938ae"),
    "clgID" : "100",
    "name" : "Vivekananda"
},

{
    "_id" : ObjectId("5cd42b5c65b41027845938ad"),
    "clgID" : "200",
    "name" : "National"
}

要点:1 =>从学院收集的全部clgID。

主题：

{
    "_id" : ObjectId("5cd42c2465b41027845938b0"),
    "name" : "Hindi",
    "members" : {
        "student" : [
            "123"
        ]
    },
    "college" : {
        "collegeID" : "100"
    }
},

{
    "_id" : ObjectId("5cd42c2465b41027845938af"),
    "name" : "English",
    "members" : {
        "student" : [
            "456",
            "789"
        ]
    },
    "college" : {
        "collegeID" : "100"
    }
}

要点:2 => Subjects集合我们将clgID映射到college.collegeID下，主题集合需要基于clgID的members.student值。

CollegeProducts

{
    "_id" : "123",
    "StudentProdcutID" : "123",
    "StudentID" : "FF80",
    "CID" : "Facebook"
},
{
    "_id" : "456",
    "StudentProdcutID" : "456",
    "StudentID" : "FF81",
    "CID" : "Facebook"
},
{
    "_id" : "789",
    "StudentProdcutID" : "789",
    "StudentID" : "FF82",
    "CID" : "Facebook"
}

要点:3 => CollegeProducts集合我们将members.student值映射到StudentProdcutID下，CollegeProducts集合我们需要在StudentID中获取值。CollegeProducts集合我们需要检查条件，CID应该是Facebook，并基于members.student取StudentID的值。

UserDetails

{
    "name" : "A",
    "StudentID" : "FF80"

},
{
    "name" : "B",
    "StudentID" : "FF81"
},
{
    "name" : "C",
    "StudentID" : "FF82"
}

要点:3 => UserDetails集合我们将StudentID值映射到StudentID下，UserDetails集合我们需要使用name的值。

预期产出：

{
"collegeName" : "National",
"StudentName" : "A"
},
{
"collegeName" : "National",
"StudentName" : "B"
},
{
"collegeName" : "National",
"StudentName" : "C"
}

我的密码

 db.Colleges.aggregate([
  { "$match": { "clgID": { "$in": ["100", "200"] }}},
  { "$lookup": {
    "from": "Subjects",
    "localField": "clgID",
    "foreignField": "college.collegeID",
    "as": "clg"
  }},
  { "$unwind": { "path": "$clg", "preserveNullAndEmptyArrays": true }},
  { "$group": {
    "_id": { "clgId": "$clg.college.collegeID", "_id": "$_id" },
    "groupDetails": { "$push": "$clg.members.student" },
    "clgName": { "$first": "$name" }
  }},
  { "$project": {
    "_id": "$_id._id",
    "clgName": 1,
    "groupDetails": {
      "$reduce": {
        "input": "$groupDetails",
        "initialValue": [],
        "in": { "$concatArrays": ["$$this", "$$value"] }
      }
    }
  }}
])

我没有得到我的预期产出，请帮助我的任何人。我使用的是mongodb版本3.4

Answer 1

如果您希望每个输出都是一个用户，就不要麻烦地分组，您只是做了两倍的工作。

将查询更改为：

    { 
        "$match" : {
            "clgID" : {
                "$in" : [
                    "100", 
                    "200"
                ]
            }
        }
    }, 
    { 
        "$lookup" : {
            "from" : "Subjects", 
            "localField" : "clgID", 
            "foreignField" : "college.collegeID", 
            "as" : "clg"
        }
    }, 
    { 
        "$unwind" : {
            "path" : "$clg", 
            "preserveNullAndEmptyArrays" : true
        }
    }, 
    { 
        "$unwind" : {
            "path" : "$clg.members.student", 
            "preserveNullAndEmptyArrays" : true
        }
    }, 
    { 
        "$project" : {
            "collegeName" : "$name", 
            "student" : "$clg.members.student"
        }
    }
], 

现在，通过第二个展开，每个对象都包含了大学名称和-一个学生，所以我们现在需要做的就是以所需的形式进行项目。

编辑:根据请求进行完整查询

    { 
        "$match" : {
            "clgID" : {
                "$in" : [
                    "100", 
                    "200"
                ]
            }
        }
    }, 
    { 
        "$lookup" : {
            "from" : "Subjects", 
            "localField" : "clgID", 
            "foreignField" : "college.collegeID", 
            "as" : "clg"
        }
    }, 
    { 
        "$unwind" : {
            "path" : "$clg", 
            "preserveNullAndEmptyArrays" : true
        }
    }, 
    { 
        "$unwind" : {
            "path" : "$clg.members.student", 
            "preserveNullAndEmptyArrays" : true
        }
    }, 
    { 
        "$lookup" : {
            "from" : "CollegeProducts", 
            "localField" : "clg.members.student", 
            "foreignField" : "StudentProdcutID", 
            "as" : "clgproduct"
        }
    }, 
    {   // can skip this unwind if theres always only one match.
        "$unwind" : {
            "path" : "$clgproduct", 
            "preserveNullAndEmptyArrays" : true
        }
    }, 
    { 
        "$match" : {
            "clgproduct.CID" : "Facebook"
        }
    }, 
    { 
        "$lookup" : {
            "from" : "UserDetails", 
            "localField" : "clgproduct.StudentID", 
            "foreignField" : "StudentID", 
            "as" : "student"
        }
    }, 
    {   // can skip this unwind if theres always only one user matched.
        "$unwind" : {
            "path" : "$student", 
            "preserveNullAndEmptyArrays" : true
        }
    }, 
    { 
        "$project" : {
            "collegeName" : "$name", 
            "student" : "$student.name"
        }
    }
], 

Answer 2

您可以使用下面的聚合

db.Colleges.aggregate([
  { "$match": { "clgID": { "$in": ["100", "200"] }}},
  { "$lookup": {
    "from": "Subjects",
    "localField": "clgID",
    "foreignField": "college.collegeID",
    "as": "clg"
  }},
  { "$unwind": { "path": "$clg", "preserveNullAndEmptyArrays": true }},
  { "$group": {
    "_id": { "clgId": "$clg.college.collegeID", "_id": "$_id" },
    "groupDetails": { "$push": "$clg.members.student" },
    "clgName": { "$first": "$name" }
  }},
  { "$project": {
    "_id": "$_id._id",
    "clgName": 1,
    "groupDetails": {
      "$reduce": {
        "input": "$groupDetails",
        "initialValue": [],
        "in": { "$concatArrays": ["$$this", "$$value"] }
      }
    }
  }},
  { "$lookup": {
    "from": "CollegeProduct",
    "localField": "groupDetails",
    "foreignField": "StudentProdcutID",
    "as": "CollegeProduct"
  }},
  { "$unwind": "$CollegeProduct" },
  { "$lookup": {
    "from": "UserDetails",
    "localField": "CollegeProduct.StudentID",
    "foreignField": "StudentID",
    "as": "Student"
  }},
  { "$unwind": "$Student" },
  { "$project": { "collegeName": "clgName", "StudentName": "$Student.name" }}
])

MongoPlayground

Output

[
  {
    "StudentName": "A",
    "_id": ObjectId("5cd42b5c65b41027845938ae"),
    "collegeName": "clgName"
  },
  {
    "StudentName": "B",
    "_id": ObjectId("5cd42b5c65b41027845938ae"),
    "collegeName": "clgName"
  },
  {
    "StudentName": "C",
    "_id": ObjectId("5cd42b5c65b41027845938ae"),
    "collegeName": "clgName"
  }
]