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

Question

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".

score 4 · Accepted Answer · answered Sep 06 '19 at 13:15

4

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.

answered Sep 06 '19 at 13:15

Sachin Vishwakarma

854
7
9

You're right, I just verified, I am using 4.0.6. Silly mistake. Thanks for the quick response. Is there an alternative to do what I am trying to achieve here? – swap_7 Sep 06 '19 at 13:25
1

I think it can be done, as the round logic is as follows. Add the value by 1/2 and take the floor of result and convert it to integer. your query will become instead of round you can use $floor: {$add:[$amount,0.5]}. $floor was introduced in 3.2 so you are safe to use that – Sachin Vishwakarma Sep 06 '19 at 13:38
But it won't retain the digits after decimal. For example, if we choose precision as 2: 100.9999+0.5 = 101.4999 -- Result (101)Fine but for 100.12+0.5 = 100.62 -- Result(100)-- and the expectation in 100.12 – swap_7 Sep 06 '19 at 13:55
ohh... let me see if i get something for you. But unfortunately i cannot be 100% sure if i can get answer for this. – Sachin Vishwakarma Sep 06 '19 at 13:58
$floor: {$add:[$amount,0.5]} works for me in 4.0.16 – sartoris Mar 26 '20 at 00:02
1

This "simple solution" cannot be often times used... imagine having some robust system in place and you want to run query over it. But you have to "just update database" instead doing some workaround. – Fusion Oct 27 '21 at 23:04

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

1 Answers1