$lookup pipeline ignore if field value is 0

Working with Data
1

Hi,

I’m pretty new to MongoDB and I have a bit of a problem.

I have the following documents in a collection

[{
_id: 1235678
price: 50000
category: mycategory
},
{
_id: 12356781
price: 65000
category: mycategory
},
{
_id: 12356781
price: 123000
category: mycategory
},
{
_id: 12356781
price: 40000
category: mycategory
}
]

then I have the following document in another collection

{
_id: 12345
minPrice: 5000
maxPrice: 12000
}

what I need to do is to $lookup all the data in the first collection and return the documents that have the “price” $gte than “minPrice” and $lte “maxPrice” … wich I do, but I encount a problem… if the value in “minPrice” and / or “maxPrice” is 0, I want to ignore it and move forward with the query and return documents based on the other values

here is what I managed to do, and it works, but if the value of “minPrice” and / or “maxPrice” is 0, it returns nothing from the db.

$lookup: {
    from: 'listings',
    let: {
        minPrice: '$minPrice',
        maxPrice: '$maxPrice',
    },
    pipeline: [
        {
            $match: {
                $expr: {
                    $and: [

                        {
                            $lte: [
                                '$price',
                                '$$minPrice'
                            ]
                        },
                           {
                            $gte: [
                                '$price',
                                '$$MaxPrice'
                            ]
                        },
                       
                    ]
                }
            }
        },
        {
            $project: {
                _id: 0
            }
        }
    ],
    as: 'matchingProperties'
}

Is there and if else like statement to check if the value in a field or let is equal to 0 and if it is to ignore that field in $expr ?

something like

(pseudo code of what I want)

if $minPrice != 0  { $lte: [  '$price', '$$minPrice'
                            ]}
 else:  {}

Thank you for you help!

3

You define

but test with

You could add a clause to test maxPrice $ne 0 and minPrice $ne 0, then add an outer $or for your else part.

4

How can I do that?

Thanks!

5 
min_or_max_is_0 = { "$or" : [ { "minPrice" : 0 } , { "maxPrice" : 0 } ] }

min_and_max_are_not_0 = { "$not" : min_or_max_is_0 }

else_query = { }

price_lte_min = { "$lte" : [ '$price' , '$minPrice' ] }

price_gte_max = { "$gte" : [ '$price' , '$maxPrice' ] }

query = { "$or" :
  [
    { "$and" : [ min_and_max_are_not_0 , price_lte_min , price_gte_max ] } ,
    { "$and" : [ min_or_max_is_0 , else_query ] }
  ]
}
2 Likes