how extract string into object

Question

I’m not sure how to explain this but I’ll write an example on how I can create a new data from this using SQL. this is from MongoDb database and I can't change any thing. I was hoping if any one Knows how to execute this using the Select method.

SELECT * FROM mytable

Original data

[{
        "id": "2433-10",
        "busiName": "ABC",
        "srTypeId": "2433-10",
        "nodeType": "0",
        "pathName": "home",
        "busiSort": 10,
        "SampleInfo": "1:sql test question identifiers: itemid:12345;itemname:Ford;itemid:12345; itemlocation=USA/itemDate=2014",
        "superTypeId": "002",}]

I want extract just SampleInfo into New data

[{
   "1":"sql test question identifiers" 
   "itemid":"12345";
   "itemname":"Ford";
   "iteminfo":"it's car";
   "itemlocation ":"USA";
   "itemDate":"2014";
}]

Answer 1

With some initial sanitization(replacing "=" with ":" and "/" with ";") maybe this is what you need: ( This is assuming that you have only single delimiter between the key/values and single delimiter between key and value )

 db.collection.aggregate([
  {
    $addFields: {
      newData: {
        "$arrayToObject": {
          "$map": {
           "input": {
             $split: [
               "$SampleInfo",
               ";"
             ]
           },
           "as": "newD",
           "in": {
             "$split": [
              "$$newD",
              ":"
            ]
          }
        }
       }
     }
    }
   }
])

Explained:

Split the SampleInfo based on delimiter ";" ( considering you have "key1:value1;key2:value2;key3:value3" in new array called newData. Split the keys and values based on the key/value delimiter ":" , convert them to "key":"value" pair in the newData array field.

playground just aggregation ( If you want to just parse and output )

playground update + agg pipleine 4.2+ ( If you want to parse and store back to the database under new field: newData )

But afcourse prefered option as suggested above is to sanitize and parse the data before inserting it to the database ...

Same thing via JavaScript Example:

     mongos> function stringToObj (string) {   var obj = {};    var stringArray = string.split(';');    for(var i = 0; i < stringArray.length; i++){      var kvp = stringArray[i].split(':');     if(kvp[1]){      obj[kvp[0]] = kvp[1]      }   }   return obj; }
     mongos> db.collection.find().forEach(function(d){  d.newData=stringToObj(d.SampleInfo);db.collection.save(d); } )
     mongos>

Explained:

1. Define JS function stringToObj ( Converting the string to object )
2. Loop over all documents via forEach and use the function to parse and modify the document adding new field newData with the content.