CODEWITHSUNDEEP
node.jsmongodbmongodb-queryaggregation-frameworkaggregation
codedump
codedump

Reputation: 387

MongoDB move data into documents between collections

I need to move data between collections as they were previously designed using relational model in mind and we're having some lookup performance issues. Note that the data shown is only an example and i need to do this for a large set of data. The data structure is as follows:

// Collection person
{
   _id: <object_id>
   ...other data
}
// Collection employments
{
    _id: <object_id>
    person_id: <person_id>,
    ... other data,
}

Now i need to move all data from employments collection to person collection based on person_id and employment_id. And i won't be deleting employments collection as I also need to move some metadata from person collection to employment collection due to some usecases.

Edit:

A person can have multiple employments. So I'll need to replace all the ids inside person by actual employments data. Also employment has person_id which needs to be replaced(can create a new property say person instead of replacing person_id) by some metadata of person(e.g. name, email..).

$merge seems promising. Will need to try it out. Seems confusing to me.

An example of result wanted:

The documents in two collections look like below:

Person Collection

{
  "_id": "619cf3045ce7329d6a8c059c",
  "name": "John Doe",
  "email": "[email protected]",
  "contact": 1234567890
}

Employment Collection

// Two documents related to person above
  // Document one
  {
    "_id": "619cf89913380c621ea8d4ec",
    "person_id": "619cf3045ce7329d6a8c059c",
    "employment_type": "Owner",
    "yearly_income": 10000000
  }
  // Document two
  {
    "_id": "619cf912374db5aec2ceb6de",
    "person_id": "619cf3045ce7329d6a8c059c",
    "employment_type": "Salary",
    "yearly_income": 12340000
  }

Expected result for person collection to be updated as

{
  "_id": "619cf3045ce7329d6a8c059c",
  "name": "John Doe",
  "email": "[email protected]",
  "contact": 1234567890,
  "employments": [
    {
    "_id": "619cf89913380c621ea8d4ec",
    "person_id": "619cf3045ce7329d6a8c059c",
    "employment_type": "Owner",
    "yearly_income": 10000000
    },
    {
      "_id": "619cf912374db5aec2ceb6de",
      "person_id": "619cf3045ce7329d6a8c059c",
      "employment_type": "Salary",
      "yearly_income": 12340000
    }
  ]
}

Expected result for employment collection to be updated as

Note: employment_type and yearly_income are not needed when the collection is updated(can be removed)

  // Document one
  {
    "_id": "619cf89913380c621ea8d4ec",
    "person_id": "619cf3045ce7329d6a8c059c",
    "person": {
      "_id": "619cf3045ce7329d6a8c059c",
      "name": "John Doe",
      "email": "[email protected]",
      "contact": 1234567890
    }
  }
  // Document two
  {
    "_id": "619cf912374db5aec2ceb6de",
    "person_id": "619cf3045ce7329d6a8c059c",
    "person": {
      "_id": "619cf3045ce7329d6a8c059c",
      "name": "John Doe",
      "email": "[email protected]",
      "contact": 1234567890
    }
  }

Any solution somewhat close to this is very much acceptable.

Upvotes: 0

Views: 147

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Try this one:

db.person.aggregate([
   {
      $lookup: {
         from: "employment",
         localField: "_id",
         foreignField: "person_id",
         as: "employments"
      }
   },
   { $merge: { into: "person" } }
])

If you like to modify collection employment then use it like this:

db.person.aggregate([
   {
      $lookup: {
         from: "employment",
         localField: "_id",
         foreignField: "person_id",
         pipeline: [{ $unset: ["employment_type", "yearly_income"] }],
         as: "employments"
      }
   },
   { $merge: { into: "person" } }
])

Upvotes: 2

Related Questions