CODEWITHSUNDEEP
stringmongoosedoubleaggregate
webmaster
webmaster

Reputation: 2020

mongoose aggregate $convert fieldname with spaces

I'm trying to convert a string field to double while aggregating on a field name that has spaces in it.

But the input field $+sumField resolves to $Total Incl Vat with spaces and this is not correct I suppose and it doesn't return the actual sum.

Anyone have a solution?

Example data

{_id: 1, "Total Incl Vat": "1550.96", customer: 1},
{_id: 2, "Total Incl Vat": "2000", customer: 1},
{_id: 3, "Total Incl Vat": "1000", customer: 1}

Aggregation

 const sumField = "Total Incl Vat";
 const $group = { 
                _id: null,
                total: {
                    $sum: {
                        $convert: {     
                            input: "$" + sumField,
                            to: 'double',
                            onNull: 0,
                            onError: "Error"
                        } 
                    }
                }
            }

 const result = mycollection.aggregate([
     { $match: { customer: 1 }}, 
     { $group }
 ]);

The aggregation result gives 0 as result which is incorrect.

Upvotes: 0

Views: 106

Answers (1)

codemonkey
codemonkey

Reputation: 7905

You can use the $toDouble operator. Here is the aggregation you're looking for:

mycollection.aggregate([
  {
    $match: {
      customer: 1
    }
  },
  {
    "$group": {
      _id: null,
      total: {
        "$sum": {
          $toDouble: "$Total Incl Vat"
        }
      }
    }
  }
])

Playground: https://mongoplayground.net/p/ItjKc31TSyi

And if you want to display just the total with a dollar sign, you can further project it like so:

  {
    "$project": {
      _id: false,
      total: {
        "$concat": [
          {
            $literal: "$"
          },
          {
            "$toString": "$total"
          }
        ]
      }
    }
  }

Which will result in:

[
  {
    "total": "$4550.96"
  }
]

Upvotes: 1

Related Questions