Problem on Sum of Mongo Aggregate (Big Number: 01 + 0.2)?

theara · March 22, 2019, 7:48am

I have data (Invoice & Payment)

// Invoice
{
        _id: "1", 
        date: , 
        name: 'Maly', 
        amount: 0.3
}
-----------------
// Payment
{
   _id : "1",
   date :  ,
   paid : 0.2,
   invoiceId: "1"
},

{
   _id : "2",
   date : ,
   paid : 0.1,
   invoiceId: "1"
}

And then I would like to check Invoice Status: Closed??? by Sum of Paid === Invoice Amount

db.invoices.aggregate([
   ............. // lookup to payment
    {
        $group:{
            _id: null,
            totalPaid: {$sum: "$paid"}
        }
    }
    ........ // project the balance field, and set status
 ])
----------------
// Result (Don't equal)
{
	"_id" : null,
	"sum" : 0.30000000000000004
}

Please help me

serkandurusoy · March 22, 2019, 11:38am

This is a typical floating point error which is essentially the same in javascript too.

Working with money, you should store your values in cents (as integer), not dollars (as double). So insteady of 0.2, you should store 20.

Then everything will add up.

robfallows · March 22, 2019, 11:44am

Note also that MongoDB now supports numberDecimal types.

In addition, there is an ECMAScript Proposal for arbitrary sized integer math.

In the fullness of time, much of the annoying behaviour of Javascript floating point math looks likely to go away.

In the meantime, @serkandurusoy’s suggestion is probably the way to go

macrozone · March 22, 2019, 11:55am

in addition to what has been said about money and floats, a general take-away:

in general you can’t equal floating points in computers. You have to use either > or<` or use a compare tolerance.

If you need precise comparison (like with money), you need decimals / integers instead of floating point numbers.

robfallows · March 22, 2019, 12:08pm

It’s also worth pointing out that there is a limit on the value of the biggest integer JavaScript can accurately represent. That’s available in Number.MAX_SAFE_INTEGER and is 2⁵³-1 (9007199254740991).

theara · March 22, 2019, 1:36pm

Thanks for all reply.
So the solution of this, we should use values in cents (as integer) by @serkandurusoy.
Eg: We will multiple all of value input with *100 (decimal number = 2 digits)

theara · March 22, 2019, 1:40pm

Now I try to insert data with NumberDecimal (In Mongo Shell)

// Invoice
{
        _id: "1", 
        date: , 
        name: 'Maly', 
        amount: NumberDecimal('0.3')
}
-----------------
// Payment
{
   _id : "1",
   date :  ,
   paid : NumberDecimal('0.2'),
   invoiceId: "1"
},

{
   _id : "2",
   date : ,
   paid : NumberDecimal('0.1'),
   invoiceId: "1"
}

And then It work fine

db.invoices.aggregate([
   ............. // lookup to payment
    {
        $group:{
            _id: null,
            totalPaid: {$sum: "$paid"}
        }
    }
    ........ // project the balance field, and set status
 ])
----------------
// Result
{
	"_id" : null,
	"sum" : 0.3
}

theara · March 22, 2019, 2:12pm

How us NumberDecimal in Meteor???

robfallows · March 22, 2019, 2:22pm

Merged as the mongo-decimal package

meteor add mongo-decimal

Although the documentation is somewhat lacking

theara · March 22, 2019, 2:25pm

Thanks again, Don’t have any document??
Could you example if you know?

robfallows · March 22, 2019, 2:28pm

I have no examples, except the Tinytest code in the package.

theara · March 22, 2019, 3:09pm

I check Decimal128on Node-Mongo driver.
But don’t understand how to use?

theara · April 3, 2019, 2:23am

Excuse me, Which one better Cents Number (as integer) VS Decimal Number???

theara · May 21, 2019, 11:23am

@robfallows, Which one better 1 VS 2:

1- Use mongo-decimal to store Mongo data as NumberDecimal

2- Use as normal Double, but we convert Double to Decimal by $toDecimal in Query Data
Ex:

Collection.aggregate([
  {
    addFields: { amountDbl: {$toDecimal: '$amount'}}
  },
  {
    $group:{_id: null, totalAmount: { $sum: "amountDbl"}}
  },
  {
    $project: {total: {$toDouble: '$total'}}
  }
])

theara · May 23, 2019, 7:29am

HI all, now I have problem when fetching data with Aggregate .
1- Meteor collection

let data = Collection.find, findOne 
---
doc.decimalField.toNumber() 
// Work fine with Decimal JS Method (Client/Server)

2- Use sakulstra:aggregate or Native node driver

let data = Collection.aggregate([.......]), Collection.rawCollection().aggregate([.....]) 
---
doc.decimalField.toNumber() 
// Don't work with Decimal JS Method (Client/Server)

Please help me…

robfallows · May 23, 2019, 10:34am

Collection.rawCollection().aggregate() returns a Promise.

If you are using it inside a Meteor Method, define your method as async and then await the result of the aggregation. You will also need to convert the aggregation cursor to an array. So something like this:

Meteor.methods({
  async doMyAggregation() {
    try {
      return await Collection.rawCollection().aggregate(aggPipeline).toArray();
    } catch(error) {
      throw new Meteor.Error(error.message);
    }
  },
});

theara · May 23, 2019, 11:45am

Yes I understand about this, but the problem is :
I can not convert Decimal field to Number by .toNumber() Decimal JS method on client/server side like .finde, findOne (Meteor collection)

robfallows · May 23, 2019, 12:16pm

Ah, OK.

The Meteor package mongo-decimal just adds a few extra methods and properties to decimal.js. It also adds EJSON compatibility.

You should probably start by looking at the NPM package linked above. I suspect you’ll probably need to convert your MongoDB value to a Decimal before you’re able to manipulate it with toNumber().

const decNum = new Decimal(mongoDecimalValue);
console.log(decNum.toNumber());

I haven’t tried any of this!

theara · May 23, 2019, 12:42pm

I tried, but get error

import { Decimal as DecJs } from 'decimal.js'

export const aggregateNumber = new ValidatedMethod({
  name: 'demo.aggregateNumber'
  validate: null,
  run() {
    if (Meteor.isServer) {
      let data = NumberVal.aggregate([
        {
          $limit: 1,
        },
      ])[0]
      data.valDecimal = new DecJs(data.valDecimal)

      return data
    }
  },
})

Error

Exception while invoking method 'demo.aggregateNumber' Error: [DecimalError] Invalid argument: 0.1
I20190523-19:42:22.037(7)?     at new Decimal (/Volumes/DATA/MeteorApp/meteor-element/node_modules/decimal.js/decimal.js:4302:15)
I20190523-19:42:22.037(7)?     at MethodInvocation.run (imports/api/demos/mongoDecimal.js:50:25)
I20190523-19:42:22.037(7)?     at ValidatedMethod._execute (packages/mdg:validated-method/validated-method.js:94:12)
I20190523-19:42:22.037(7)?     at MethodInvocation.demo.aggregateNumber (packages/mdg:validated-method/validated-method.js:57:23)
I20190523-19:42:22.038(7)?     at maybeAuditArgumentChecks (packages/ddp-server/livedata_server.js:1767:12)
I20190523-19:42:22.038(7)?     at DDP._CurrentMethodInvocation.withValue (packages/ddp-server/livedata_server.js:719:19)
I20190523-19:42:22.038(7)?     at Meteor.EnvironmentVariable.EVp.withValue (packages/meteor.js:1304:12)
I20190523-19:42:22.038(7)?     at DDPServer._CurrentWriteFence.withValue (packages/ddp-server/livedata_server.js:717:46)
I20190523-19:42:22.038(7)?     at Meteor.EnvironmentVariable.EVp.withValue (packages/meteor.js:1304:12)
I20190523-19:42:22.038(7)?     at Promise (packages/ddp-server/livedata_server.js:715:46)
I20190523-19:42:22.038(7)?     at new Promise (<anonymous>)
I20190523-19:42:22.038(7)?     at Session.method (packages/ddp-server/livedata_server.js:689:23)
I20190523-19:42:22.039(7)?     at packages/ddp-server/livedata_server.js:559:clock430:

theara · May 23, 2019, 12:46pm

I tried to console valDeciaml

"valDecimal": { "_bsontype": "Decimal128", "bytes": { "0": 1, "1": 0, "2": 0, "3": 0, "4": 0, "5": 0, "6": 0, "7": 0, "8": 0, "9": 0, "10": 0, "11": 0, "12": 0, "13": 0, "14": 62, "15": 48 } } }