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
– Arvind Pal, Commented Jul 21, 2021 at 11:43
@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
– CK5, Commented Jul 21, 2021 at 11:45
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
– Rubén Vega, Commented Jul 21, 2021 at 11:48

Rajdeep D · Accepted Answer · 2021-07-21 11:50:52Z

4

I just corrected this, please take a look

Playground

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

answered Jul 21, 2021 at 11:50

Rajdeep D

3,9201 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert the string to integer in mongo aggregate query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related