skip to Main Content

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

2

Answers


  1. 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
    }
    
    1. Afterwards $project required fields
    2. 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"
        }
      }
    ])
    
    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search