I have a collection called "datas" and the other collection is named as "aidretentionandgraduations" in a database called "challenge". Both collections have similar values stored in different field called unitId and Unitid, their values should be used to merge those two collections. So if unitId==elevatorInfo.Institution_Characteristics.Unitid display the documents else don't dispaly. below is what I tried:
db.aidretentionandgraduations([
{
'$lookup': {
'from': 'datas',
'localField': 'Unitid',
'foreignField': 'unitId',
'as': 'nice'
}
}, {
'$unwind': {
'path': '$nice'
}
}, {
'$match': {
'$expr': {
'$eq': [
'$unitId', '$elevatorInfo.Institution_Characteristics.Unitid'
]
}
}
}
])
data collection:
{ "_id": { "$oid": "627f925ffa5e617f51d5632e" }, "elevatorInfo": { "Institution_Characteristics": { "Unitid": "139384", "Name": "Georgia Northwestern Technical College", "City": "Rome", "State": "GA", "Web_Address": "www.gntc.edu/", "Distance_Learning": "Offers undergraduate courses and/or programs" } }, "studentCharges": { "Cost": { "Published_Tuition_And_Required_Fees": "", "In-state": "$3,062", "Out-of-state": "$5,462", "Books_And_Supplies": "$1,500", "Off-campus_(not_With_Family)_Room_And_Board": "$5,528", "Off-campus_(not_With_Family)_Other_Expenses": "$5,191", "Off-campus_(with_Family)_Other_Expenses": "$2,431", "Total_Cost": "", "Off-campus_(not_With_Family),_In-state": "$15,281", "Off-campus_(not_With_Family),_Out_Of_State": "$17,681", "Off-campus_(with_Family),_In-state": "$6,993", "Off-campus_(with_Family),_Out-of-state": "$9,393" }, "Level_of_student": { "Undergraduate": { "In-state": "$3,062", "Out-of-state": "$5,462" }, "Graduate": { "In-state": "", "Out-of-state": "" } } }}
aidretentionandgraduations collection:
{ "_id": { "$oid": "622ce9ba5d72be4d703e972d" }, "financialAid": { "Student_Financial_Aid": { "All_Undergraduate_Students": { "Percent_receiving_aid": "", "Average_amount_of_aid_received": "" }, "Any_Grant_Or_Scholarship_Aid": { "Percent_receiving_aid": "90%", "Average_amount_of_aid_received": "$5,603" }, "Pell_Grants": { "Percent_receiving_aid": "69%", "Average_amount_of_aid_received": "$7,845" }, "Federal_Student_Loans": { "Percent_receiving_aid": "8%", "Average_amount_of_aid_received": "$3,371" }, "Full-time,_First-time,_Degree/certificate-seeking_Undergraduate_Students": { "Percent_receiving_aid": "", "Average_amount_of_aid_received": "" } } }, "retentionAndGraduation": { "Retention_And_Graduation": { "Overall_Graduation_Rates": { "Rate": " " }, "Total": { "Rate": "49%" }, "Men": { "Rate": "57%" }, "Women": { "Rate": "40%" }, "Nonresident_Alien": { "Rate": "100%" }, "Transfer_Out-rate": { "Rate": "7%" } } }, "unitId": 139384, "__v": 0}
Final output stored as uni:
{ "_id": { "$oid": "622fffe6b9ccae37d3bd3b92" }, "financialAid": { "Student_Financial_Aid": { "All_Undergraduate_Students": { "Percent_receiving_aid": "", "Average_amount_of_aid_received": "" }, "Any_Grant_Or_Scholarship_Aid": { "Percent_receiving_aid": "90%", "Average_amount_of_aid_received": "$5,603" }, "Pell_Grants": { "Percent_receiving_aid": "69%", "Average_amount_of_aid_received": "$7,845" }, "Federal_Student_Loans": { "Percent_receiving_aid": "8%", "Average_amount_of_aid_received": "$3,371" }, "Full-time,_First-time,_Degree/certificate-seeking_Undergraduate_Students": { "Percent_receiving_aid": "", "Average_amount_of_aid_received": "" } } }, "retentionAndGraduation": { "Retention_And_Graduation": { "Overall_Graduation_Rates": { "Rate": " " }, "Total": { "Rate": "49%" }, "Men": { "Rate": "57%" }, "Women": { "Rate": "40%" }, "Nonresident_Alien": { "Rate": "100%" }, "Transfer_Out-rate": { "Rate": "7%" } } }, "unitId": 139384, "studentCharges": { "Cost": { "Published_Tuition_And_Required_Fees": "", "In-state": "$3,062", "Out-of-state": "$5,462", "Books_And_Supplies": "$1,500", "Off-campus_(not_With_Family)_Room_And_Board": "$5,528", "Off-campus_(not_With_Family)_Other_Expenses": "$5,191", "Off-campus_(with_Family)_Other_Expenses": "$2,431", "Total_Cost": "", "Off-campus_(not_With_Family),_In-state": "$15,281", "Off-campus_(not_With_Family),_Out_Of_State": "$17,681", "Off-campus_(with_Family),_In-state": "$6,993", "Off-campus_(with_Family),_Out-of-state": "$9,393" }, "Level_of_student": { "Undergraduate": { "In-state": "$3,062", "Out-of-state": "$5,462" }, "Graduate": { "In-state": "", "Out-of-state": "" } } }, "elevatorInfo": { "Institution_Characteristics": { "Unitid": "139384", "Name": "Georgia Northwestern Technical College", "City": "Rome", "State": "GA", "Web_Address": "www.gntc.edu/", "Distance_Learning": "Offers undergraduate courses and/or programs" } }, "__v": 0}
CodePudding user response：
Use $out or $merge aggregate stages. I didn't exactly understand what is your problem with $merge since you didn't provide your code, but if you need modify output of $lookup, you can do it via $project before $out or $merge.
CodePudding user response：
the real problem was type casting. I failed to notice that unitId was a number(int32) but Unitid was a string, because of this aggregation operation was not carried out. Mongodb community forum helped me to realize such a trivial mistake. Here is the link to that. The code with type conversion is as follows:
[
{
'$addFields': {
'unitId': {
'$toString': '$unitId'
}
}
}, {
'$lookup': {
'from': 'datas',
'localField': 'unitId',
'foreignField': 'elevatorInfo.Institution_Characteristics.Unitid',
'as': 'nice'
}
}, {
'$unwind': {
'path': '$nice'
}
}, {
'$project': {
'__v': 0,
'_id': 0
}
}
]