$lookup and filtering

Consider two models ‘cars’ and ‘bookings’. car modal has fields like carName, registrationNumber, totalCapacity. The project is that users can rent cars for particular days, and their bookings will be stored in the ‘bookings’ modal which includes the date, objectId of the car, and user details.

Please help me to create one query which gives list of available cars on searched date by checking in ‘bookings’ model.

Please share sample documents from all you collections. Also share expected results for the documents you shared.

You wrote model rather than collections. Are you using mongoose or something like it?

I am using mongoose.





If the user input is like, he wants to see cars that are available on 01-11-2022. The desired output is that car with _id:2. Because a car with id 1 is booked for the date 01-11-2022 which we can see in bookings .

I want this filtering in one query. I have heard about the $lookup and pipeline but I don’t know how to apply it. Please help :slight_smile:

What you need is an outer pipeline $lookup with localField:_id and foreignField:carId that has a inner pipeline that $match your $tripDate. The the outer pipeline terminates with a $match stage that filters out non-empty array (result of the $lookup).

See https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/#correlated-subqueries-using-concise-syntax.