MongoDB query $and that returns documents that matches all fields progressively

Question

I am trying to query an array of cars. This is an example of my data set:

[
{
    "model": "Model 3",
    "location": "Canada",
    "color": "green",
    "engine": "1.4 turbo",
    "gearbox": "automatic",
    ...
},
{
    "model": "Model 4",
    "location": "Brazil",
    "color": "green",
    "engine": "1.4 turbo",
    "gearbox": "automatic",
    ...
}
]

This is my query:

Car.paginate(
      {
        model: new RegExp(`^${req.body.model}$`, 'i'),
        color: new RegExp(`^${req.body.color}$`, 'i'),
        ...
      },
      { offset, limit, sort: getSort(req.query.sortType), populate: ['car'] }
    )

This query is using an implicit $and but that's not what I want to do. I want to return all the documents that satisfy one or more of the query but also that takes into account all queries. So, if I search for "model 1" it will return all cars that are named "model 1" and if I search for "model 1" and "black" it should return all cars that are named "model 1" and are of the color "black" (NOT all "model 1" AND all color "black").

Also, the way I am doing it right now, all fields are required to exist in my req.body. I would like for it to not require all the fields, only search with the fields it's given.

Does this answer your question? [How to search in array of object in mongodb](https://stackoverflow.com/questions/14040562/how-to-search-in-array-of-object-in-mongodb) — whoami - fakeFaceTrueSoul, Aug 30 '20 at 05:15
In this example he is using it for a field (award). I tried to use the $elemMatch operator but I keep getting the "top level operator doesn't exist" error. That is because I think it only works for a field. I don't think this would work for the whole document, at least I was not able to make it work. Plus, you can't use $elemMatch with strings. — Windbox, Aug 30 '20 at 05:20

Montgomery Watts · Accepted Answer · 2020-08-30T06:48:28.007

1

In your endpoint you would want to build the query object before making the call to paginate(). This way you can check for certain fields in the request body to determine if they should be added. It might look like this:

var query = {};
if (req.body.model) {
  query.model = new RegExp(`^${req.body.model}$`, 'i');
}
if (req.body.color) {
  query.color = new RegExp(`^${req.body.color}$`, 'i');
}

This way the query will only contain fields in the request body. You would then pass query to paginate() like this:

Car.paginate(
      query,
      { offset, limit, sort: getSort(req.query.sortType), populate: ['car'] }
    )

If there are other fields you want to include in the query by default you can add them when it is first declared.

edited Aug 30 '20 at 06:48

answered Aug 30 '20 at 06:11

Montgomery Watts

3,806
2
10
24

Hi there, unfortunately $or wouldn't work in this case because I would like for it to consider all the fields. So if I query for a "Model 1", "Black" it should return all the cars named "Model 1" that have the color "Black". $or would return cars that are "Black" but with other model names, like "Model 3" or whatever. – Windbox Aug 30 '20 at 06:19
I see, I thought it was already doing a logical AND. – Montgomery Watts Aug 30 '20 at 06:22
What do you mean by this: "I want to return all the documents that satisfy one or more of the query but also that takes into account all queries." – Montgomery Watts Aug 30 '20 at 06:35
I perhaps was a bit confused because of all the testing I was doing. The logical AND is almost what I want. The issue with it is that right now it requires my req.body to contain all the fields for the query. In this case req.body.model, and req.body.color. If I try to query it with only req.body.model it won't work, and I would like for it to work and I would to be able to add more fields to my req.body as I wish. – Windbox Aug 30 '20 at 06:40

MongoDB query $and that returns documents that matches all fields progressively

1 Answers1