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
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.
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.
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.
$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)
Hi Susnigdha! I was just wondering whether you are working anywhere and would like to know about the Tech Stack that you specialize in. I did notice that you have a deep interest in Automation and Cyber Security so I was just curious whether you’d be interested in guiding and mentoring. Waiting to hear from you. It’d be great if we can talk over the phone.