How can I move mongodb all documents in one collection to another collection that has different schema?

Question

There are 15,000 documents in collection

This is old collection

[
    {
       "_id" : ObjectId("611f0b9f9964fea718ccea5f"),
       "quotationNO" : "Q-000001",
       "note": "21-8-2021<->send to DC<->John<#>21-8-2021<->OK<->Bob"
     }
     {
       "_id" : ObjectId("611f2afa9964fea718ccea9c"),
       "quotationNO" : "Q-000002",
       "note": "22-8-2021<->send to DC<->Bob"
      }
]

This is new collection . I want to modify note field from string to object array like this. what is the best solution to do?

[
    {
       "_id" : ObjectId("611f0b9f9964fea718ccea5f"),
       "quotationNO" : "Q-000001",
       "note": [
          {
            "data": "21-8-2021",
            "message": "send to DC",
            "user": "John"
          },
          {
            "data": "21-8-2021",
            "message": "OK",
            "user": "Bob"
          }
       ]
     }
     {
       "_id" : ObjectId("611f2afa9964fea718ccea9c"),
       "quotationNO" : "Q-000002",
       "note": [
          {
            "data": "22-8-2021",
            "message": "send to DC",
            "user": "Bob"
          }
        ]
      }
]

Answer 1

Chain up $split and $map to split your note string and create the desired object. Finally do a $merge to upsert into new_collection.

db.collection.aggregate([
  {
    "$addFields": {
      "note": {
        "$split": [
          "$note",
          "<#>"
        ]
      }
    }
  },
  {
    "$addFields": {
      "note": {
        "$map": {
          "input": "$note",
          "as": "n",
          "in": {
            $split: [
              "$$n",
              "<->"
            ]
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "note": {
        "$map": {
          "input": "$note",
          "as": "n",
          "in": {
            "data": {
              "$arrayElemAt": [
                "$$n",
                0
              ]
            },
            "message": {
              "$arrayElemAt": [
                "$$n",
                1
              ]
            },
            "user": {
              "$arrayElemAt": [
                "$$n",
                2
              ]
            }
          }
        }
      }
    }
  },
  {
    "$merge": {
      "into": "new_collection",
      "on": "_id",
      "whenMatched": "replace",
      "whenNotMatched": "insert"
    }
  }
])

Here is the Mongo Playground for your reference.

Answer 2

You can try following these steps:

1. $project required fields and $split note by <#>
2. Afterwards using JS $function build from obtained arrays new objects by splitting elements by <-> separator and assign function result to new field note;

function(new_note){
  let result = [];
  for(let i = 0; i < new_note.length; i++){
   const nested =  new_note[i].split('<->');
   result.push( {data:nested[0], message:nested[1],user:nested[2]});
   } 

   return result
}

3. Afterwards $project required fields
4. Use MongoDb $merge to save data in new collection.

db.collection.aggregate([
  {
    $project: {
      new_note: {
        $split: [
          "$note",
          "<#>"
        ]
      },
      quotationNO: 1
    }
  },
  {
    $addFields: {
      note: {
        $function: {
          body: "function(new_note){let result = []; for(let i = 0; i < new_note.length; i++){ const nested =  new_note[i].split('<->'); result.push( {data:nested[0], message:nested[1],user:nested[2]}); } return result}",
          args: [
            "$new_note"
          ],
          lang: "js"
        }
      }
    }
  },
  {
    $project: {
      note: 1,
      quotationNO: 1
    }
  },
  {
    $merge: {
      into: "new_collection",
      on: "_id",
      whenMatched: "replace",
      whenNotMatched: "insert"
    }
  }
])