Convert string to int in aggregate in mongoDB using nodejs

Question

Sample Data stored in mongoDB::

TotalStudent,TotalPresent all are in string.

[{'_id':'12sdsd','TotalStudent:'1,233,273','TotalPresent':'23'},
{'_id':'22fdf','TotalStudent:'2,445,232','TotalPresent':'32'}]

Node js:: I tried to Aggregate and give me the output of the total number of students and total number of present.

P.S.: I have used mongoose to fetch data from mongoDB.

covid_data.aggregate([{
        $group:{
            _id:"",
            TotalStudents:{$sum:{$toInt:"$TotalStudents"}},
            TotalPresent:{$sum:{$toInt:"$TotalPresent"}}
        }
    }], (err,docs)=>{
        if(err){
            console.log(err);
            return res.status(500).send(err);
        }
        else{
            console.log(docs);
            res.send(docs);
        }
    });

It gives me error as::

"Failed to parse number '1,233,273' in $convert with no onError value: Bad digit \",\" while parsing 1,233,273"."codeName":"ConversionFailure"

Edit1

Edited sample data's TotalStudents number format. As it also contains ,. Previously I failed to include the TotalStudents contains number as "1,233,273" and "2,445,232".Because of which while trying to convert I'm getting the error.

Any help will be really appreciated. Cheers!!

Answer 1

Starting MongoDB version >= 4.0 you can use $toInt to convert string to int, Try below code :

covid_data.aggregate([
    {
      $group: {
        _id: "",
        TotalStudents: { $sum: { $toInt: "$TotalStudent" } },
        TotalPresent: { $sum: { $toInt: "$TotalPresent" } }
      }
    }
  ], (err,docs)=>{
    if(err){
        console.log(err);
        return res.status(500).send(err);
    }
    else{
        console.log(docs);
        res.send(docs);
    }
});

Test : mongoplayground

Note : In your $group stage, _id has to be _id: "" in order to group on all documents(Using _id:"$_id" will result in returning almost same data as is as _id is unique for each doc). Plus you've couple of typos at $sum, at {$sum:"TotalPresent"} also at {$Sum:"$TotalStudents"}.

Update : Updated answer with new requirement : As original query is failing due to having , : '1,233,273'.

db.collection.aggregate([
   /** Re-create two string fields without `,` */
  {
    $addFields: { 
    TotalStudent: {
        $reduce: {
          input: { $split: [ "$TotalStudent", "," ] },
          initialValue: "",
          in: { $concat: [ "$$this", "$$value" ] }
        }
      },
    TotalPresent: {
        $reduce: {
          input: { $split: [ "$TotalPresent", "," ] },
          initialValue: "",
          in: { $concat: [ "$$this", "$$value" ] }
        }
      }
    }
  },
  {
    $group: {
      _id: "",
      TotalStudents: { $sum: { $toInt: "$TotalStudent" } },
      TotalPresent: { $sum: { $toInt: "$TotalPresent" } }
    }
  }
])

Test : mongoplayground

We need to split string based on delimiter , & concat all the strings in array to one string using $reduce. This can be avoided by use of $replaceOne starting MongoDB version >= 4.4, Check this :: how-to-replace-substring-in-mongodb-document , Use this link in case if you wanted to update data.

Note : I would highly suggest to consider my above comment :

So the concept everyone is talking about is : write it once in a perfect way to read it n times with ease.. So now we need to do n num of tricks to make it work each time we read it, So do you wanna update all data with numbers ? - I would say to do it or even I can provide a query which will work but I would not opt it over updating data to correct format.