How to use $lookup and $in mongodb aggregate

问题:

Colleges

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

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

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

Subjects:

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

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

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.

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"
}

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

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

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

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"
}

My Code

 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"] }
      }
    }
  }}
])

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

回答1:

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

Change your query to this:

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

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.

EDIT: full query according to request

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

回答2:

You can use below aggregation

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"
  }
]