How to create Price Filter with multiple conditions in MongoDB?

byMR

February 5, 2022

I’m trying to create an e-commerce website which has multiple filters.

I’m having trouble understanding the right way to apply numeric filters.

Here is the list of my filters for pricing a product:

0-500 USD

501-1000 USD

1001-5000 USD

5001-10000 USD

10000-20000 USD

20000+ USD

I tried $and with multiple price filter. User selects 0-500 & 1000-5000 filter

Query:

{
 "category":"jeans",
 "$and":
   [
     {"price":{"$gt":1000}},
     {"price":{"$lte":5000}},
     {"price":{"$gt":0}},
     {"price":{"$lte":500}}
   ]
}

I tried $and with array of $or. User selects 0-500 & 1000-5000 filter

Query:

{
  "category":"jeans",
  "$and":
   [
    {"$or":[{"price":{"$gt":1000}},{"price":{"$lte":5000}}]},
    {"$or":[{"price":{"$gt":0}},{"price":{"$lte":500}}]}
   ]
}

I get incorrect results with both approaches.

What’s the right approach here?

>Solution :

The following will return 0 results, because the product has to be greater than 1000, but less than 500, which is impossible.

{
 "category":"jeans",
 "$and":
   [
     {"price":{"$gt":1000}},
     {"price":{"$lte":5000}},
     {"price":{"$gt":0}},
     {"price":{"$lte":500}}
   ]
}

You may want to use

{
  "category":"jeans",
  "$or":
   [
    {"$and":[{"price":{"$gt":1000}},{"price":{"$lte":5000}}]},
    {"$and":[{"price":{"$gt":0}},{"price":{"$lte":500}}]}
   ]
}

https://mongoplayground.net/p/dk7jqJ_qo1S