Query documents with conditions which are based on subdocuments

Question

I have a room schema like this:

let roomSchema = new mongoose.Schema({
  events: [{type: mongoose.Schema.ObjectId, ref: 'Event'}],
  name: { type: String, required: true, index: { unique: true } }
});

It contains an array of event ids. Event schema:

let eventSchema = new mongoose.Schema({
  title: { type: String, required: true },
  room: { type: mongoose.Schema.ObjectId, ref: 'Room', required: true },
  date: { type: Date, required: true },
  slot: { type: Number, required: true }
});

What I am trying to do, is:
"query all rooms, that don't contain events of a particular date AND slot".

So if the date from the request matches the date of a room AND the slot, then that room should not be in the response. If only one of the fields match it should be in the response.

I found similar questions here, but none for my scenario:
https://stackoverflow.com/a/36371665/5115768
Mongoose query where value is not null

I tried something like:

this.model.find(req.query).populate({
  path: 'events',
  match: {
    date: { $ne: req.query.date },
    slot: { $ne: req.query.slot }
  }
}).exec((err, rooms) => {

  rooms = rooms.filter((room) => {
    return room.events != null;
  });
  
  res.status(200).json(rooms);
});

But of course it doesn't work (rooms is always empty array). I have a really hard time figure this out.

How can I query documents (rooms) with conditions which are based on subdocuments (events)?

UPDATE

I changed my schema and code so that slot is not an array anymore.

If I understood @Veeram's solution correctly, it can't be used, because it would return empty events arrays for "reserved rooms". The problem with this is that I need to filter out these rooms with empty events array, which would include rooms that didn't have any events associated in the first place (these shouldn't be filtered out).

Now I managed to get all "reserved rooms" (the ones that contain an event that matches req.query.date AND req.query.slot):

this.model.find(req.query).populate({
  path: 'events',
  match: {
    $and: [
      { date: { $eq: date } },
      { slot: { $eq: slot } }
    ]
  }
}).exec((err, reservedRooms) => {
  reservedRooms = reservedRooms.filter(room => room.events.length > 0);
  res.status(200).json(reservedRooms);
});

This is the exact opposite of what I want but it's a start, how can I "reverse" that?

Verify populate works without any match and then try `match: { date: { $ne: req.query.date }, slot: { $nin: [req.query.slot] } }`. If it works then it will output an empty events array when there is no match and you can filter rooms based on empty check on events array. — s7vr, Dec 26 '17 at 17:26
Sorry I was not clear and didn't cover all cases. For events that don't exist you will not have an events field in the room so nothing to populate there and the events field will not be there. For events that are present it will populated with the matching events based on your matching criteria. For example; `{room:"room1"}` with no events, `{room:"room2", events:[]}` with no matching events and `{room:"room3", events:[{date:2017, title:"event1"]}` for matching events. So essentially when you get the output back you just have to show all rooms where event array exists. — s7vr, Jan 10 '18 at 20:03
Alternatively you can add the query criteria to req.query to only consider the events that exist for population. Something like `{events:{$exists:true}}`. This way what you get is your final output. — s7vr, Jan 10 '18 at 20:31
Another option if you not interested in events data and only need rooms you can run the $lookup aggregation query by joining to events collection on server side and return matching rooms only in the output response. — s7vr, Jan 10 '18 at 20:33
Could you post an example please? I could not get this to work, I tried with your first solution and returned all rooms where `events` array exists: `let freeRooms = rooms.filter(room => typeof room.events !== "undefined" && room.events !== null); res.status(200).json(freeRooms);` but the result are ALL rooms with their `events` array empty. — lenny, Jan 10 '18 at 20:54
So what output do you get ? Is the events array not getting filtered correctly ? Population applies each criteria to every element in the events array. Do you want to apply the criteria on a whole array like where in a events array there is **no** element matching your criteria. — s7vr, Jan 10 '18 at 20:58
It's just empty for each room. And the room which should be filtered out is still in the response. — lenny, Jan 10 '18 at 21:03

score 0 · Answer 1 · answered Dec 26 '17 at 02:41

0

Have you tried:

match: {
    $not: [{
        $and: [
            date: { $eq: req.query.date },
            slot: { $in: req.query.slot }
        ]
    }]
}

answered Dec 26 '17 at 02:41

Rico Chen

2,260
16
18

Stupid question to ask but since you updated you question, have you tried updating `$in` to `$eq`? – Rico Chen Dec 26 '17 at 21:06
yes, also my comment was wrong. your code returns `unknown top level operator: $not`. – lenny Dec 26 '17 at 22:24

s7vr · Accepted Answer · 2018-01-11T02:57:44.020

Populate applies the matching criteria for each event in event array.

So when applied negation on each element of event array the distinction is lost between arrays where there is available event (match) versus when there is no available event (no match) when you get the populated array.

So you have to use server $lookup to apply criteria on a whole array.

The below aggregation query will filter rooms where events ( when present ) doesn't not contain the document with date and slot as given in query.

Use $elemMatch to compare the query criteria on the same element and $not to return the rooms where no array elements contain the query criteria.

this.model.aggregate([
  {
    "$match":{"events":{"$exist":true}}
  },
  {
    "$lookup": {
      "from": "events", // Collection name not model or schema name
      "localField": "events",
      "foreignField": "_id",
      "as": "events"
    }
  },
  {
    "$match":{"$not":{"$elemMatch":{"date": date, "slot":slot}}}
  }
]).exec((err, reservedRooms) => {});

This will output the rooms with events. You can remove the events from the final output by using $project with exclusion. Add {$project:{"events":0}} as last stage.

this does not get me the correct results. still getting room with an event that matches the criteria. — lenny, Jan 11 '18 at 02:06
It appears to working with test data I've created. Can you please provide some data for testing ? — s7vr, Jan 11 '18 at 02:07

Query documents with conditions which are based on subdocuments

UPDATE

2 Answers2