databasemongodbmongodb-compassimport-csv

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


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. Please click here to see the image

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.


Solution

  • 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.