Mongoose not bringing in any results

Question

I'm trying to build a kind of complex query in mongoose, my problem is all the parameters this query can receive are non mandatory and that is bringing me problems. Right now I did this:

let query = {
            publicationType: req.body.searchParams.publicationType
        };
        if (req.body.searchParams.address) {
            query.address = {};
            if (req.body.searchParams.address.city) {
                query.address.city = req.body.searchParams.address.city;
            }
        }
        try {
            const dwellings = await model.Dwelling.find({
                $and: [{$or: [{publicationType: req.body.searchParams.publicationType}]},
                    {
                        $or: [{
                            address: [query.address]
                        }]
                    }]
            }).lean().exec();
            console.log(dwellings);
            res.send({dwellings});
        } catch (err) {
            next(err);
        }
    });

This was to try out if the city parameter is working, if I search only by publication type I get the desired results, if I add city I get 0 results even when I'm supposed to get more, I debugged mongoose to see how the query was being built and it's like this:

Mongoose: dwelling.find({ '$and': [ { '$or': [ { publicationType: 'Venta' } ] }, { '$or': [ { address: { '$in': [ { city: 'La Plata' } ] } } ] } ] }, { fields: {} })
[]

Answer 1

so with the guidness of drew Maclnnis i figured out how to make it work at least

const query = {};
        if (req.body.searchParams.publicationType !== '') {
            query['publicationType'] = req.body.searchParams.publicationType;
        }
        if (req.body.searchParams.address) {
            if (req.body.searchParams.address.streetNumber !== undefined) {
                query['address.streetNumber'] = req.body.searchParams.address.streetNumber;
            }
            if (req.body.searchParams.address.streetName !== undefined) {
                query['address.streetName'] = req.body.searchParams.address.streetName;
            }
            if (req.body.searchParams.address.city !== undefined) {
                query['address.city'] = req.body.searchParams.address.city;
            }
            if (req.body.searchParams.address.state !== undefined) {
                query['address.state'] = req.body.searchParams.address.state;
            }
            if (req.body.searchParams.address.country !== undefined) {
                query['address.country'] = req.body.searchParams.address.country;
            }
            if (req.body.searchParams.address.zip !== undefined) {
                query['address.zip'] = req.body.searchParams.address.zip;
            }
        }
        if (req.body.searchParams.price) {
            if (req.body.searchParams.price.min !== undefined) {
                query['price'] = {$gt: req.body.searchParams.price.min};
            }
            if (req.body.searchParams.price.max !== undefined) {
                query['price'] = {$lt: req.body.searchParams.price.max};
            }
        }
        try {
            const dwellings = await model.Dwelling.find({
                '$and': [
                    query
                ]
            }).lean().exec();
            console.log(dwellings);
            res.send({dwellings});
        } catch (err) {
            next(err);
        }

can this be optimized?

Answer 2

In your code you are building an $and expression of two $or expressions but each $or expression has only one argument each, so you are effectively building this $and find expression (note, I'm using the mongo shell syntax below):

db.dwellings.find({ 
   '$and': [ 
       { publicationType: 'Venta' }, 
       { address: { '$in': [ { city: 'La Plata' } ] } } 
   ]
});

... in words: "find the dwellings with publicationType Venta and address.city La Plata.

if I add city I get 0 results even when I'm supposed to get more

Since you mention you expect to get more results when you add in address.city I suspect you really want the top-level expression to be $or not $and, like this:

db.dwellings.find({ 
   '$or': [ 
       { publicationType: 'Venta' }, 
       { address: { '$in': [ { city: 'La Plata' } ] } } 
   ]
});

You may want to experiment more with your query in mongo shell before expressing it as code. If you happen to have Docker available on your workstation, I find running mongo in a container a useful way to prototype things while developing, for example:

docker run --name mongo -d mongo:3.7
# after mongo is up and running...
docker exec -it mongo mongo

In the interactive mongo shell you can insert documents for testing and experiment with find expressions, example:

use stackoverflow;
dwell1 = { publicationType: 'Venta', address: { city: 'Somewhere' } };
dwell2 = { publicationType: 'Something', address: { city: 'La Plata' } };
db.dwellings.insert(dwell1);
db.dwellings.insert(dwell2);

# Try results of $and
db.dwellings.find({ '$and': [ 
   { publicationType: 'Venta' }, 
   { address: { '$in': [ { city: 'La Plata' } ] } } 
]  });

# ... compare to results of $or
db.dwellings.find({ '$or': [ 
   { publicationType: 'Venta' }, 
   { address: { '$in': [ { city: 'La Plata' } ] } } 
]  });

... once you have inserted some documents for testing, confirm whether you want an $and or $or expression in your find.