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!!

Data types are important, use numbers instead of string when you create your collections, at least in your db instance. If your data comes in that way you can always handle it in a different way when you import it. If you don't do that you will ended up having an Armageddon of workarounds instead of a query. — Yak O'Poe, May 05 '20 at 15:38
Thing is I scrapped the data from a website and got the data from overthere. And stored the data in mongoDb. Because of which I didn't have control on what type it saves the data — Ajax, May 05 '20 at 15:41
I understand you very well, but your data has to be sanified if you want reliable results. If what you do is a "one shot" operation, you can risk and skip it but... If your data is incremental, a layer that fix consistency might be needed. — Yak O'Poe, May 05 '20 at 16:00
@Ajax if you scrapped the data, that means you are the one who can control how the data enters the database. Otherwise you might want to iterate through the data and update it to an appropriate format at the application layer. — thammada.ts, May 05 '20 at 16:08
@Ajax : 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.. — whoami - fakeFaceTrueSoul, May 05 '20 at 16:13
Thank you all for your responses.....I will update my data to apprpriate format which be best practice. I will accept the answer of @whoami as it works for the properly formated data. — Ajax, May 05 '20 at 17:11

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-05-05T16:23:25.807

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.

I've edited my question as my number which in this case is a string also contains "," which i failed to include because of which it's not able to convert. Can you please have a look and suggest me. — Ajax, May 05 '20 at 15:38

Convert string to int in aggregate in mongoDB using nodejs

1 Answers1