Update a collection field based on another collection

Working with Data
#1

Hi,

I need to update a collection field based on the values from another collection, For eg:
collection 1 - employee
values
{"_id":1,“name”:“aaa”, “department”:11}
{"_id":2,“name”:“bbb”, “department”:12}

collection 2 - department
{"_id":11,“name”:“dept1”}
{"_id":12,“name”:“dept2”

I need the result collection as
values
{"_id":1,“name”:“aaa”, “department”:{"_id":11,“name”:“dept1”}}
{"_id":2,“name”:“bbb”, “department”:{"_id":12,“name”:“dept2”}}

Please let me know how can I update the existing column or create new column in the collection to get the result like the above

Thanks,

#2

To that sort of things, you use the aggregation framework. You can read about it https://docs.mongodb.com/manual/aggregation/. MongoDB University also offer the M121 course related to it.

You will need a $lookup aggregation stage. Find more at https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

It is not clear if you want to add the field to the employee collection or if you want a third collection. If the latter, you can achieve that with $out stage. More at https://docs.mongodb.com/manual/reference/operator/aggregation/out/

I recommend to go via $out even if the goal is to add the field in the first collection. This way you do not screw up existing data and it gives you the opportunity to verify the results and simply swap the collection when you are happy.

I also recommend using Compass to develop the pipeline as it makes it easy to experiment. You can export the pipeline to your favorite programming language once you are done. This being said, I prefer to keep my pipeline in their natural json form in a file that I can reuse in the shell or in my favorite language.

3 Likes
#3

You can perform a join, but a join is based a particular equality or an expression. If _id in the second table, is 10 + _id in the first table, then the join might be possible.

However, the piped value is returned as an array, therefore, the array needs to be ‘unwinded’ and then manipulated into the document you want.

Finally, you need to push this data into a new collection, which is quite easy once you actually get the data.

#4

It is $lookup in MongoDB. Join are SQL. I really do not understand what you mean by if _id is 10+ in the first table. Could you please elaborate?

The exact stage is called $unwind as documented in https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/

Quite right, manipulated with $project and/or $addFields.

And finally

That’s the $out stage already mentioned.

1 Like
#5

$lookup is the operator to perform a join, you’re right.

A person wants to join the documents, but a person needs some sort of expression to actually compare one document with another. I thought the expression is -

_id(document in coll 1) = 10 + _id(document in coll 2)

#6

The _id in collection 1 is not related at all to the _id of collection 2.

What’s related is the field “department” in collection 1 and _id of collection 2.

1 Like