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"
          }
        ]
      }
]

CodePudding user response：

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.

CodePudding user response：

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"
    }
  }
])

CodePudding user response：

Are you using an API from some language, for instance, pymongo if Python, etc.? Are you using the mongo shell? In any case, I think the only option you have to rewrite your documents with a different schema.

Let's see: .- the field "note" from the old collection has to be parsed taking the different patterns I can detect by sight into account.

The 1st message has a note filed which contains two notes, splitted by a #, right? The 2nd one, just one note So, I think if you split by '#', you can have a note separator. Then, on a particular note, splitting by "<->" can give you a filed separator inside the note. Seems fields are in order, so, take each document from the old collection, treated as well, copy the produced document into the new collection.

Is it enough by now? Do you need more help?