MongoDB copy a field to another collection with a foreign key
copy, database, foreign-keys, mongodb
Solution
There are several issues with your test set up:
- Case of field names does not match (you are referencing `color` instead of `Color` when copying)
- Only one of the example foreign keys matches in the target collection: `ObjectId('515f7db83f71d6bcb1c41a48')`
- Your update will only affect the first matching document for the "foreign key". This would be fine for a 1:1 relationship, but not a 1:many
A corrected example taking the above into account (aside from the non-matching keys):
db.test1.User.find().forEach(
function(x) {
db.test2.Car.update(
// query
{ userID: x._id },
// update
{ $set: { color: x.Color} },
// options:
{ "multi" : true } // Update all matching documents
);
}
);
Which results in setting `{color:blue}` for the only foreign key that actually matches in the sample documents:
db.test2.Car.find()
{
"_id" : ObjectId("515f84883f71d6bcb1c41a55"),
"speed" : 202,
"userID" : ObjectId("515f7db83f71d6bcb1c41a49")
}
{
"_id" : ObjectId("515f84883f71d6bcb1c41a56"),
"speed" : 193,
"userID" : ObjectId("515f7db83f71d6bcb1c41a4a")
}
{
"_id" : ObjectId("515f84883f71d6bcb1c41a54"),
"color" : "blue",
"speed" : 291,
"userID" : ObjectId("515f7db83f71d6bcb1c41a48")
}
Problem
I want to copy over the color from user collection to the Car collection. I'm using a foreign key which is userID. ``` > db.test1.User.find() { "_id" : ObjectId("515f7db83f71d6bcb1c41a48"), "age" : 33, "Color" : "blue" } { "_id" : ObjectId("515f7dc03f71d6bcb1c41a49"), "age" : 52, "Color" : "red" } { "_id" : ObjectId("515f7dc43f71d6bcb1c41a4a"), "age" : 43, "Color" : "yellow" } > db.test2.Car.find() { "_id" : ObjectId("515f84883f71d6bcb1c41a54"), "speed" : 291, "userID" : ObjectId("515f7db83f71d6bcb1c41a48") } { "_id" : ObjectId("515f84883f71d6bcb1c41a55"), "speed" : 202, "userID" : ObjectId("515f7db83f71d6bcb1c41a49") } { "_id" : ObjectId("515f84883f71d6bcb1c41a56"), "speed" : 193, "userID" : ObjectId("515f7db83f71d6bcb1c41a4a") } ``` Here is my query ``` db.test1.User.find().forEach( function(x) { db.test2.Car.update( { userID: x._id }, { $set: { color: x.color} } ) } ); ``` I want this result: ``` > db.test2.Car.find() { "_id" : ObjectId("515f84883f71d6bcb1c41a54"), "speed" : 291, "userID" : ObjectId("515f7db83f71d6bcb1c41a48"), "color" : "blue" } { "_id" : ObjectId("515f84883f71d6bcb1c41a55"), "speed" : 202, "userID" : ObjectId("515f7db83f71d6bcb1c41a49"), "color" : "red" } { "_id" : ObjectId("515f84883f71d6bcb1c41a56"), "speed" : 193, "userID" : ObjectId("515f7db83f71d6bcb1c41a4a"), "color" : "yellow" } ``` Thanks for your help !