db={rooms:[{"_id":{"$oid":"65ef799f331ab6aef4fae5ba"},"number":"1","length":"10","width":"12","__v":0},{"_id":{"$oid":"65ef79a0331ab6aef4fae5be"},"number":"2","length":"10","width":"12","__v":0}],kennels:[{"_id":{"$oid":"65ef79a2331ab6aef4fae5d4"},"REF_RoomID":{"$oid":"65ef799f331ab6aef4fae5ba"},"identifier":"1.1.6x10","active":true,"partition":"1","length":10,"width":6,"__v":0},{"_id":{"$oid":"65ef79a2331ab6aef4fae5d5"},"REF_RoomID":{"$oid":"65ef799f331ab6aef4fae5ba"},"identifier":"1.2.6x10","active":true,"partition":"2","length":10,"width":6,"__v":0},{"_id":{"$oid":"65ef79a3331ab6aef4fae5e5"},"REF_RoomID":{"$oid":"65ef79a0331ab6aef4fae5be"},"identifier":"2.1.4x10","active":true,"partition":"1","length":10,"width":4,"__v":0},{"_id":{"$oid":"65ef79a3331ab6aef4fae5e6"},"REF_RoomID":{"$oid":"65ef79a0331ab6aef4fae5be"},"identifier":"2.2.4x10","active":true,"partition":"2","length":10,"width":4,"__v":0},{"_id":{"$oid":"65ef79a3331ab6aef4fae5e7"},"REF_RoomID":{"$oid":"65ef79a0331ab6aef4fae5be"},"identifier":"2.3.4x10","active":true,"partition":"3","length":10,"width":4,"__v":0}],bookings:[{"_id":{"$oid":"65ef88f444e7d6607498ac2e"},"REF_KennelID":{"$oid":"65ef79a2331ab6aef4fae5d4"},"year":2024,"dayOfYear":100,"duration":6,"__v":0},{"_id":{"$oid":"65f0ca69f2667460e600a46a"},"REF_KennelID":{"$oid":"65ef79a2331ab6aef4fae5d4"},"year":2024,"dayOfYear":107,"duration":1,"__v":0}]}

db.kennels.aggregate([{$match:{active:true}},{$lookup:{from:"rooms",let:{searchRoomID:"$REF_RoomID"},pipeline:[{$match:{$expr:{$and:[{$eq:["$_id","$$searchRoomID"]},{$in:["$number",["1","2"]]}]}}}],as:"room"}},{$unwind:{path:"$room",preserveNullAndEmptyArrays:true}},{$lookup:{from:"bookings",let:{searchKennelID:"$_id"},pipeline:[{$match:{$expr:{$and:[{$eq:["$REF_KennelID","$$searchKennelID"]},{$eq:["$year",2024]},{$in:["$dayOfYear",[100,101,102,103]]}]}}}],as:"bookings"}},{$unwind:{path:"$bookings",preserveNullAndEmptyArrays:true}},{$group:{_id:"$_id",identifier:{$first:"$identifier"},active:{$first:"$active"},partition:{$first:"$partition"},length:{$first:"$length"},width:{$first:"$width"},bookings:{$push:"$bookings"}}},{$project:{"bookings.REF_KennelID":0,"bookings.__v":0}}])