How to update object_id to cross collections?

MongoDB Atlas
#1

Hi Team,

I have two collections for example Collection A and B, B collection have business_id column(integer), I want to update A collection Object_id to B collection business_id based on A collection id field and B collection business_id? How to do this?

#2

I do not understand your use-case very well.

Please post sample documents from both collections and the desired results.

Do you want to store back the result permanently?

1 Like
#3

Simple @steevej I need simple update command.

In the above example i have two collections. One collection name is users and another one collection name is user_business.

Users collections have object_id example {“_id”: ObjectId(“64a6b0f5af3af7f59a05ba33”) and i have business_id (integer) column in user_business table.

I want to update Users collection Object_id value to User_businesses collection business_id (integer) based on where condition source_collection.id = target_collection.id.

#4

I have used below script but not working,

var usersCollection = db.getCollection("users");
var userBusinessCollection = db.getCollection("user_businesses");

usersCollection.find({}).forEach(function(userDoc) {
    var businessDoc = userBusinessCollection.findOne({ business_id: userDoc.business_id });
    if (businessDoc) {
        usersCollection.updateOne({ _id: userDoc._id }, { $set: { object_id: businessDoc.business_id } });
    }
});
#5

You should check the result of updateOne().

Is you updateOne really executed? May be it is never executed because businessDoc is always null.

Does userDoc really have a field named business_id? We do not know for sure because we do not have sample data.

Is your code matching any document? If not the query part is wrong.

Is userDoc updated with the wrong value? If so the the $set part is wrong.

This where it is important to supply

What I suspect is that businessDoc is always null.

What I do not understand is why do you do this update? From what I see userDoc already has business_id, so why duplicate this value in a new field object_id?

#6

Business id column have integer value like 1,2,5,…etc, so that i want User table object_id act as foreign key in user_business table business_id column.

#7

The above query executed successfully without any errors, but values are not updated.

#8

This is my point. You do not update when businessDoc is null so

If this is the case, then the code will be

Also

If businessDoc matchexs the query business_id:userDoc.business_id, the setting object_id to businessDoc.business_id will duplicate in userDoc the field business_id.

It is hard to help you. If the update is not happening the the issue is your code or your data. Possible issues with the code have been identified but have not been addressed and despite my request for sample data you still prefer to describe it.

#9

Hi @steevej,

I have checked matching records using below command.

db.users.find().forEach(function(userDoc) {
  var businessDoc = db.user_businesses.findOne({ business_id: userDoc._id });
  if (businessDoc) {
    print("Matching user _id:", userDoc._id, "with business_id:", businessDoc.business_id);
  }
});

It’s fetching matching records and it’s working fine.

#10

When i am updating Users table UUID (Object_id) using below command getting error

db.users.find().forEach(function(userDoc) {
  var matchingBusinessDoc = db.user_businesses.findOne({ business_id: userDoc.id });
  if (matchingBusinessDoc) {
    db.user_businesses.updateOne(
      { business_id: userDoc.id },
      { $set: { business_id: userDoc._id: ObjectId} }
    );
  }
});

Is this possible one table UUID to another table act as foreign key? if the above code is incorrect please give me correct one.

In the above code getting syntax error. at { $set: { business_id: userDoc._id: ObjectId} }

#11

Good. We are going somewhere. That seems to confirm

because the findOne query is not the same compared to the original code.

original

and the one that works

As for the error about in

I would try the $set that you had in the original code

#12

Is this still an issue?

Have you made any progress?

If the issue is resolved please share the solution with the rest of us and mark one post as the solution. This would help future reader with similar issue.

#14

Dear @william_worse,

you should not have reply to my post with spam. It has been flagged to the crew. I have started following you. I have marked your other post as spam too.

I hate spammers.

#15

@KRISHNAKUMAR_K, please follow up on your posts. I have read most of your other threads and you let them die without providing the final solution you implemented. This behavior is detrimental to the usefulness of this forum. Other users will lose time reading your posts and their replies without knowing what works and what does not.

3 Likes