如何在mongodb聚合中使用$ lookup和$ [英] How to use $lookup and $in mongodb aggregate

问题描述

学院

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

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

点:1 =>将所有clgID从Colleges集合中获取.

Point : 1 => Take all clgID From Colleges collection.

主题:

{
    "_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集合，我们在college.collegeID下映射了clgID，主题集合，我们需要基于clgID取members.student的值.

Point : 2 => Subjects collection we are mapped clgID under college.collegeID, Subjects collection we need to take the values of members.student based on clgID.

学院产品

{
    "_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集合，我们在StudentProdcutID下映射了members.student值，CollegeProducts集合，我们需要获取StudentID中的值. CollegeProducts集合，我们需要检查条件CID应该为Facebook，并基于members.student取StudentID的值.

Point : 3 => CollegeProducts collection we are mapped members.student values under StudentProdcutID, CollegeProducts collection we need to take the values in StudentID. CollegeProducts collection we need to check condition CID should be Facebook and take the values of StudentID based on members.student.

UserDetails

UserDetails

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

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

点:3 => UserDetails集合，我们在StudentID下映射了StudentID值，UserDetails集合我们需要获取name的值.

Point : 3 => UserDetails collection we are mapped StudentID values under StudentID, UserDetails collection we need to take the values of name.

预期输出:

Expected Output:

{
"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 version3.4

I am not getting my expected output,kindly help me anyone. i am using mongodb version3.4

推荐答案

如果您希望将每个输出作为一个用户，则不要打扰分组，您只需做两倍的工作即可.

Don't bother grouping if you want the the each output to be one user, you're just doing double the work.

将查询更改为此:

    { 
        "$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"
        }
    }
], 

第二步展开时，每个对象都包含大学名称和-ONE-学生，因此我们现在要做的就是以所需的形式进行项目设计.

Now with the second unwind each object contains the college name and -ONE- student so all we need to do now is project in the required form.

根据要求进行完整查询

    { 
        "$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"
        }
    }
], 

这篇关于如何在mongodb聚合中使用$ lookup和$的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！