Search code examples
mongodbmatchroundingexpr

How to fix 'Unrecognized expression $round' while applying it to stored data in pipeline using mongodb $and condition with $lookup


I have applied precision to the data coming from DB, while viewing it in browser using Angular 7.
Front End: Angular 7
Back End: Java and Mongodb

before precision (in db): 100.9999
after precision visible to user(2 after decimal): 101.00

There is a search functionality on UI in which a user can write any amount range.
Scenario 1:
User input: 100 to 100
Result: no records found (expected)

Scenario 2:
User input: 101 to 101
Result: no records found (should have found 1 record according to user(101), but since the actual value is 100.9999, it won't return any)

Solution, I am trying to apply precision before even applying the search criteria in query.
Code before the solution:

 "$pipeline": [{
        "$match": {
            "$expr": {
               "$and": [{
                          "$eq": ["$column1", "$valueForColumn1"]
                        },
                        {
                          "$gte": ["$amount", "$minValue"]
                        },
                        {
                          "$lte": ["$amount", "$maxValue"]
                        }]
                      }
                    }
                  }]

SQL equivalent is:

Select * from table_name where minVale >= ROUND(amount) <= maxValue;

I tried the following code, but it gives the error.
Code after applying the solution:

  "$pipeline": [{
        "$match": {
            "$expr": {
               "$and": [{
                          "$eq": ["$column1", "$valueForColumn1"]
                        },
                        {
                          "$gte": [{"$round": ["$amount", 2]}, "$minValue"]
                        },
                        {
                          "$lte": [{"$round": ["$amount", 2]}, "$maxValue"]
                        }]
                      }
                    }
                  }]

I get the following error while using "$round" under $and and $gte/$lte


Assert: command failed {
    ok: 0,
    errmsg: Unrecognised expression "$round"
    code: 168
    codeName: InvalidPipeline Operator

Can anyone tell, what am I doing wrong and how should I do it.

NOTE: I am also using $lookup in the query, may be that is why it is not able to recognise "$round".


Solution

  • One of the reason i can see is the version of mongodb you are using is not supporting $round operator. $round operator was introduced in version 4.2... and above. according to do Mongodb doc

    Upgrade your mongodb version and try.