MongoDb: What's the best approach to modify a model field from an array of strings to an array of ids that would refer to another model?

Question

I have a Profile model, that contains this field:

  interests: {
    type: [String],
  },

My app has been running for a while. So this means for several documents, this field has already been filled with an array of strings.

In order to achieve certain goals, I need to create a model Interest with a field name and then refer to it in the Profile like this:

  interests: [{
    type: Schema.Types.ObjectId,
    ref: "interests",
  }],

The field name should contain the already existing string interests in Profile.interests. This is the approach that I think I will follow:

Create Interest model.
Fill name field with the existing Profile.interests strings.
a. When doing this replace Profile.interests with the _ids of the newly created Interest documents.
b. Make sure Interest.name is unique.
c. Remove spaces.
Wherever interests in the app are used in the backend, use populate to fill them.

This doesn't feel like a safe operation. So I would like to hear your thoughts on it. Is there a better approach? Should I avoid doing this?
Thank you.

user_8987128 · Answer

Step 1:

Create a Model for interests,

specify your desired fields fir interests schema and set properties for particular fields
specify collection name in options as per your requirement
create a model and specify your desired name in model

const InterestsSchema = new mongoose.Schema(
    { name: { type: String } },
    { collection: "interests" }
);
const Interests = mongoose.model("Interests", InterestsSchema);

Instead of removing interests field add new field interest (you can choose desired field), for safe side whenever you feel the current update working properly you can remove it, Update profile schema,

update interest field as per your requirement, now newly added field is interest

  interests: {
    type: [String]
  },
  interest: [{
    type: Schema.Types.ObjectId,
    ref: "interests"
  }],

Step 2:

Wherever interests in the app are used in the backend, use interest and populate to fill them.

Step 3: (just execute the query)

Make a collection for interests and store all unique interests string from profile collection, so write a aggregation query to select unique string and store in interests collection, you can execute this query in mongo shell or any editor that you are using after specifying your original profile collection name,

$project to show interests field only because we are going to deconstruct it
$unwind to deconstruct interests array
$group by interests and select unique field, and trim white space from interests string
$project to show name field and if you want to then add your desired fields
$out will create a new collection interests and write all interests with newly generated _id field

db.getCollection('profile').aggregate([
  { $project: { interests: 1 } },
  { $unwind: "$interests" },
  { $group: { _id: { $trim: { input: "$interests" } } } },
  { $project: { _id: 0, name: "$_id" } },
  { $out: "interests" }
])

Playground

You have example input:

[
  {
    "_id": 1,
    "interests": ["sports","sing","read"]
  },
  {
    "_id": 2,
    "interests": ["tracking","travel"]
  }
]

After executing above query the output/result in interests / new collection would be something like:

[
    {
      "_id": ObjectId("5a934e000102030405000000"),
      "name": "travel"
    },
    {
      "_id": ObjectId("5a934e000102030405000001"),
      "name": "sports"
    },
    {
      "_id": ObjectId("5a934e000102030405000002"),
      "name": "read"
    },
    {
      "_id": ObjectId("5a934e000102030405000003"),
      "name": "tracking"
    },
    {
      "_id": ObjectId("5a934e000102030405000004"),
      "name": "sing"
    }
  ]

Step 4: (just execute the query)

Add new field interest with reference _ids from interests collection in profile collection, there are sequence to execute queries,

find profile query and project only required fields _id and interests when interest (new field) field is not exists and iterate loop using forEach
trim interests string iterating loop through map
find the interests reference _id by its name field from created interests collection
update query for add interest field that have _ids in profile collection

db.getCollection('profile').find(
  { interest: { $exists: false } }, 
  { _id: 1, interests: 1 }).forEach(function(profileDoc) {

    // TRIM INTEREST STRING
    var interests = profileDoc.interests.map(function(i){
        return i.trim();
    });

    // FIND INTERESTS IDs
    var interest = [];
    db.getCollection('interests').find(
      { name: { $in: interests } }, 
      { _id: 1 }).forEach(function(interestDoc){
        interest.push(interestDoc._id);
    });

    // UPDATE IDS IN PROFILE DOC
    db.getCollection('profile').updateOne(
        { _id: profileDoc._id },
        { $set: { interest: interest } }
    );

});

You have example input:

[
  {
    "_id": 1,
    "interests": ["sports","sing","read"]
  },
  {
    "_id": 2,
    "interests": ["tracking","travel"]
  }
]

After executing above query the result in your profile collection would be something like:

  [
    {
      "_id": 1,
      "interests": ["sports","sing","read"],
      "interest": [
        ObjectId("5a934e000102030405000001"),
        ObjectId("5a934e000102030405000002"),
        ObjectId("5a934e000102030405000004")
      ]
    },
    {
      "_id": 2,
      "interests": ["tracking","travel"],
      "interest": [
        ObjectId("5a934e000102030405000000"),
        ObjectId("5a934e000102030405000003")
      ]
    }
  ]

Step 5:

Now you have completed all the steps and you have newly added interest field and also old field interests field is still in safe mode, just make sure everything is working properly you can delete old interests field,

remove old field interests field from all profiles

db.getCollection('profile').updateMany(
  { interests: { $exists: true } }, 
  { $unset: { "interests": 1 } }
);