Convert the string to integer in mongo aggregate query

Question

My company has inserted numerical values for certain keys in string format. They can't be converted to integer format for some business reason.

Now coming to the query...

I am writing a mongo aggregate query which calculates annual cost for a particular manufacturer like Unilever across shops. It seems I cannot convert a string to integer inside the $cond and $eq blocks using $toInt method.

Please find below the sample collection.

[
 {
  _id: "ddfdfdfdggfgfgsg",
  rate: "3323",
  quantity_packs: "343",
  shop_name: "Whole Foods",
  manufacturer_name: "Unilever"
 },
 {
  _id: "ddfdfdfsdsds",
  rate: "434",
  quantity_packs: "453",
  shop_name: "Carrefour",
  manufacturer_name: "Unilever"
 },
 {
  _id: "dfdfdgcvgfgfvvv",
  rate: "343",
  quantity_packs: "23",
  shop_name: "Target",
  manufacturer_name: "Beirsdorf"
 }
]

The query is

db.collection.aggregate([
    {
        $match: {
            manufacturer_name: {
                $in: [ "Unilever" ]
            }
        }
    }, 

    {
        $group: {
            _id: {
                "Shop Name": "$shop_name"
            },

            "annual_cost": {
                $sum: {
                    $cond: [
                        {
                            $eq: ["manufacturer_name", "Unilever"]
                        },
                        { "$toInt": "$rate"}, 
                        0
                    ]
                }
            },

            "other_annual_cost": {
               $sum: {
                   $cond: [
                      {
                         $ne: [$manufacturer_name, "Unilever"]
                      }, {"$toInt" : "$rate"},
                      0
                  ]
               }
            },

            "annual_qty": {
                $sum: {
                    "$toInt": "$quantity_packs"
                }
            },
        }
    },

    {
        $project: {

            "Purchase_Cost": {
                $multiply: [ "$annual_cost", "$annual_qty" ]
            },

            "Other Manu Pur Cost": {
                $multiply: ["$other_annual_cost", "$annual_qty"]
            }
        }
    }
])

Current Output

[
  {
    _id: { 'Shop Name': 'Whole Foods' },
    Purchase_Cost: 0
  }
]

As $rate is of string type, the multiplication has yielded 0 as shown over here. Ideally the result should show some integer value for purchase cost as shown below.

Intended Output

[
  {
    _id: { 'Shop Name': 'Whole Foods' },
    Purchase_Cost: 234
  }
]

Any suggestion would be of great help. I want to make this query work somehow.

I have updated the question based on Rajdeep's Answer.

you can get help from here docs.mongodb.com/manual/reference/operator/aggregation/toInt — Arvind Pal
@ArvindPal I know $toInt does the type conversion. But how to use it and where to use it in my context? I want to convert the $rate to integer before I use it in $cond block. — CK5
The $toInt is working fine, the trouble is that your "annual_cost" result is 0, so at the end when you multiply it, you get 0 aswell. What is your expected value in "annual_cost"? Why ara you using sum on a condition? — Rubén Vega

Rajdeep Debnath Rajdeep Debnath · Accepted Answer · 2021-07-21T11:50:52

I just corrected this, please take a look

Playground

"annual_cost": {
        $sum: {
          $cond: [
            {
              $eq: [
                "$manufacturer_name", //added $
                "Unilever"
              ]
            },
            {
              $toInt: "$rate" //added $toInt
            },
            0
          ]

Convert the string to integer in mongo aggregate query

1 Answers