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

#1

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

#2

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.

1 Like
#3

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 :slight_smile:

1 Like
#4

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.

2 Likes
#5

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 253-1 (9007199254740991).

#6

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)

image

#7

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
}

:sunny: :blush:

#8

How us NumberDecimal in Meteor???

#9

Merged as the mongo-decimal package

meteor add mongo-decimal

Although the documentation is somewhat lacking :frowning_face:

#10

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

#11

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

1 Like
#12

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

#13

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

#14

@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'}}
  }
])
#15

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…

#16

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);
    }
  },
});
Which one better `Double VS Decimal` in Mongo?
#17

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)

#18

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!

#19

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:
#20

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