Decimal128 addition during the group stage results in a lot of decimal numbers

Working with Data
1

Hello,

I was aggregating some data using the group stage and I have noticed that some result sets have obtained very long decimal point sequences despite being stored as Decimal128 values:

    {
      count: 6,
      totalValue: new Decimal128("3.82"),
      courier: 'Name Surname',
      zipcode: '12345'
    },
    {
      count: 1137,
      totalValue: new Decimal128("1196.109999999999999822364316059975"),
      courier: 'Name Surname',
      zipcode: '12350'
    },

The group stage:

{
        "$group": {
            "_id": {
                "zipcode": "$address.zipcode",
                "courierId": "$bag.courier.userId",
                "name": "$bag.courier.name"
            },
            "count": {
                "$sum": 1
            },
            "totalValue": {
                "$sum": "$priceData.price"
            }
        }
    },

$priceData.price is stored as Decimal128 on the database. Could you tell me what could be the cause of the issue and how to resolve it, please? Thank you

2

Hi @Vladimir

It may pay to check your dataset as something other than Decimal128 may have been inserted leading to a loss of precision in the $sum

See this playground with mixed types and the resulting aggregation:

Vs this one filtering only the decimal128 values:

1 Like
3

That’s a very nice catch, thank you. Running the following query:

{ $priceData.price: { $not : { $type : 19 } } }

Revealed some old documents that have the price value stored as strings instead of Decimal128.

What do you think would be the right course of action? 2 ideas come to mind:

  1. Repair the incorrect fields by running an update query and converting them to the correct type (string → Decimal128). However, I am not sure what the correct and safe update query could be?

  2. As an immediate solution, I have used the $round: ["$price", 2] operator in the $project stage to reduce the decimal points. What are your thoughts on that?

Again, thank you very much

1 Like
4

Due to type bracketing they won’t be included in the sum, it would be another number type. Another number type would create this result or a decimal created by passing a long as an argument(this was accpted in earlier versions).

If there is no use case for the data being of the unexpected decimal then I would suggest updating it to the expected decimal with correct precision.

Could be as simple as using $toDecimal, $round and $merge or it could be more complex if there are strings or null present in which case $convert and some $cond might have to e used.

:+1: :+1:

1 Like
5

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.