Relations between two documents in 2 different collections using “db.collection.updateMany”

Hey everyone, I wonder what is the best way to create relations between two documents in 2 different collections using “db.collection.updateMany”?

Let’s say I have one Companies collection:

[
  { _id: 1, name: “tesla”, location: “somewhere”},
  { _id: 2, name: “google”,location: “somewhereElse” },
]

And one Employees collection:

[
  { _id: 154, name: “John Doe”, company: “tesla” },
  { _id: 155, name: “Joe Jones”, company: “google” },
  { _id: 156, name: “Alon Gal”, company: “tesla” },
]

I know I can use the $lookup operation in order to fetch the right document during a query but I was wondering if it’s possible to run some “updateMany” function to add another field to the companies collection in order to get the following results:

[
  { _id: 1, name: “tesla”, location: “somewhere” employees: [{name: “Alon Gal”}, {name: “John Doe”}]},
  { _id: 2, name: “google”,location: “somewhereElse” }
]

So basically make some “match” between two fields in two different collections that share the same value (company field in employees collection and name field in companies collection in this case) and update one of these collections accordingly.

I know also about DBrefs but it seems that I need to know the document’s _id and this is not the case here since I want to make the match according to another field’s value.

Hi @Alon_Gal ,

When thinking about the presented model it sounds that the company information inside the employee data will make much more sense.

The reason for this is that potentially having all employees in a company document will make a massive array anti pattern and might hit a document size limit. On the countrary an employee will have a limited number of companies making it compelling to embed a company in ita document.

When looking for employees for q certain company you can index a company name or id in that collection and moving employees between companies is much easier by just updating the company in the employee document.

Now to perform this task on existing data you can use 2 options:

  1. Script a bulk find and update going through the employee collection and fetching placing each company.
  2. Do a $merge command with a $lookup back to the employee collection. Each employee will lookup its company details and place it using merge.

I recommend reading the following:

Thanks
Pavel

Thanks Pavel, I appreciate it and will look into it!