How do I merge two different collections of same database into a new collection having similar field values in MongoDB compass?

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 is what I am trying to achieve. I used $lookup aggregattion but the as in $lookup aggregation inserts all documents in the collection.
the datacollection:

{  "_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": ""      }    }  }}

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

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

below is the final output I am tryin to achieve:

Looks like you didn’t set your foreignField value correctly. Didn’t you say that it’s stored in field elevatorInfo.Institution_Characteristics.Unitid - you have to put the entire string not just the last part of the field path into foreignField. Also, localField has a capitalization issue, it should be unitId, not Unitid.

You also don’t need any $match after unwind because the $lookup will only match documents where the two values are equal.

Asya

I tried that multiple times and although what you said is the correct way to do it. It didn’t workout for me so
I deliberately kept wrong foreignField as well as capitalized unitId to make it work. It makes no sense at all.
below u can see the newly fomed array named nice has no values:


if I use the wrong foreigneField and lowercase unitId it works but all the db.datas documents are combined with single document of db.aidretentionandgraduation which need to be filterd using values of unitId and Unitid to display matching values

Your aggregation will not work.

unitId in aidretentionandgraduations collection is a number

elevatorInfo.Institution_Characteristics.Unitid in datas is a string

  1. You need to migrate one collection to use the same data type as the other.
    or
  2. Compare by doing a type conversion on the fly.

migrating datas to use number will be better that migrating the other to use string

2 Likes

please do forgive me but could you elaborate more on mentioned solutions… It’s just been two days since I started using mongodb as a part of internship challenge. I am assuming the problem lies trying to manipulate two different types without any type casting, but it’s difficult understanding solutions, any similar links or past question to the similar problem would be highly appreciated

The string

inside object

from the

IS NOT EQUAL TO the number

from

Yes this is exactly what I wrote.

Just like JS:

Atlas atlas-d7b9wu-shard-0 [primary] test> unit_id = "369"
369
Atlas atlas-d7b9wu-shard-0 [primary] test> Unit_Id = 369
369
Atlas atlas-d7b9wu-shard-0 [primary] test> unit_id === Unit_Id
false
// but
Atlas atlas-d7b9wu-shard-0 [primary] test> unit_id === Unit_Id.toString()
true
// or
Atlas atlas-d7b9wu-shard-0 [primary] test> parseInt(unit_id) === Unit_Id
true

Inside aggregation you can use $convert.

However since

I recommend that you take the M001, M121 from https://university.mongodb.com/.

1 Like

thanks a lot :grinning:… I have been searching for this solution all over google and stackoverflow.

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.