How to set relationship between fields of imported CSV files in mongodb database-CodePudding

I have successfully imported a csv file into my mongodb database using mongodb compass, while importing the file, I had the choice to select/choose the data type of a field but when I try to change a field that is related to another collection to ObjectId, I got this error:

Argument passed in must be a string of 12 bytes or a string of 24 hex characters or an integer

so I had to leave the field as string even though the field is a relationship to another field in another collection within the same database. See the image bellow:

Click image to see larger view.

from the image, the olist_order_items_dataset has a field product_id and seller_id which are in a relationship with olist_products_dataset and olist_sellers_dataset collections respectively but these fileds are imported as a strings rather than as an ObjectId. So the problem is how do I make the relationship between these fields?.

I have tried to change the fields to ObjectId within mongodb compass but I will have to do this for every document and I have 112, 650 documents in the collection.

CodePudding user response：

Within MongoDB Compass you can run mongosh (lower-left in GUI).

After use leadDevAssignment you could do something like:

db.olist_order_items_datasets.update({
  "product_id": {"$type": "string"},
  "seller_id": {"$type": "string"}
},
[
  {
    "$set": {
      "product_id": {"$toObjectId": "$product_id"},
      "seller_id": {"$toObjectId": "$seller_id"}
    }
  }
],
{"multi": true}
)

Try it on mongoplayground.net.

Or, if you want to do it from the Compass Aggregation, you could do it like:

db.olist_order_items_datasets.aggregate([
  {
    "$match": {
      "product_id": {"$type": "string"},
      "seller_id": {"$type": "string"}
    }
  },
  {
    "$set": {
      "product_id": {"$toObjectId": "$product_id"},
      "seller_id": {"$toObjectId": "$seller_id"}
    }
  },
  {"$merge": "olist_order_items_datasets"}
])

Try it on mongoplayground.net.