How to merge 2 collections with specific fields in mongodb, with aggregate & $lookup

Question

I am having issue to get this to work, with aggregate & $lookup. So, your prompt help is appreciated.

Let's say, I have these 2 collections, in the same mongoDB:

// collection1 (2 records, in this example):
{
  name: 'name1',
  city: 'city1'
}

{
  name: 'name2’,
  city: 'city2’
}

// collection2 (3 records, in this example):
{
  name: 'name1',
  education: ‘college’,
  occupation: ’engineer’,
  address: 'address1'
}

{
  name: 'name2’,
  education: ‘highschool’,
  occupation: ’manager’,
  address: 'address2'
}

{
  name: 'name3’,
  education: ‘highschool’,
  occupation: ’manager’,
  address: 'address3'
}

I want a final collection, final_collection, returned like this (name field is used as an index):

{
  name: 'name1',
  city: 'city1'
  education: ‘college’,
  occupation: ’engineer’
}

{
  name: 'name2’,
  city: 'city2’
  education: ‘highschool’,
  occupation: ’manager’
}

Essentially, adding only the 'education' and 'occupation' fields into collection1, for matched records (name1 and name2).

Can you please help? Much thanks in advance!

Answer 1

You could you $lookup to join both collections

• $lookup to join two collections
• $addFields to add new field
• $arrayElementAt to get first element after the join. $ifNull to prevent NPE if there is no element in array

Here is the code

db.collection2.aggregate([
  {
    "$lookup": {
      "from": "collection1",
      "localField": "name",
      "foreignField": "name",
      "as": "join"
    }
  },
  {
    $addFields: {
      join: {
        $ifNull: [
          { "$arrayElemAt": [ "$join", 0 ] },
          []
        ]
      }
    }
  },
  {
    "$addFields": {
      "city": "$join.city",
      join: "$$REMOVE",
      address: "$$REMOVE"
    }
  }
])

Working Mongo playground