Mongoose query on multiple populated fields

Question

I have three collections i.e Events, News and FuneralNews. I have another Notifications collection that is just a combination of all three and contains the reference Id of either/one of the three collections.

I want to fetch only those Notifcations whose eventId OR newsId OR funeralNewsId field isActive is true

EventsSchema:

var EventsSchema = new Schema({
  ...
  isActive: Boolean
});

FuneralNewsSchema:

var FuneralNewsSchema = new Schema({
  ...
  isActive: Boolean
});

NewsSchema:

var NewsSchema = new Schema({
  ...
  isActive: Boolean
});

NotificationSchema:

var NotificationSchema = new Schema({
  type: {
    type: String,
    required: true
  },
  creationDate: {
    type: Date,
    default: Date.now
  },
  eventId: {type: Schema.Types.ObjectId, ref: 'Events'},
  newsId: {type: Schema.Types.ObjectId, ref: 'News'},
  funeralNewsId: {type: Schema.Types.ObjectId, ref: 'FuneralNews'},
  organisationId: {type: Schema.Types.ObjectId, ref: 'Organization'}
});

This was my query before I need a check on isActive property of referenced collection:

  let totalItems;
  const query = { organisationId: organisationId };

  Notification.find(query)
    .countDocuments()
    .then((count) => {
      totalItems = count;
      return Notification.find(query, null, { lean: true })
        .skip(page * limit)
        .limit(limit)
        .sort({ creationDate: -1 })
        .populate("eventId")
        .populate("newsId")
        .populate("funeralNewsId")
        .exec();
    })
    .then((notifications, err) => {
      if (err) throw new Error(err);
      res.status(200).json({ notifications, totalItems });
    })
    .catch((err) => {
      next(err);
    });

Now I don't know how to check on isActive field of three populated collections prior population.

I have seen other questions like this and this but being a newbie can't edit it according to my use-case. Any help would be highly appreciated

Answer 1

use $lookup for each objectId refrence then group by _id of null to get data and add myCount as total number put original data to array and use unwind to destruct array and use addField

model
  .aggregate([
    {
      $lookup: {
        from: "Events", // events collection name
        localField: "eventId",
        foreignField: "_id",
        as: "events",
      },
    },
    {
      $lookup: {
        from: "FuneralNews", //FuneralNews collection name
        localField: "funeralNewsId",
        foreignField: "_id",
        as: "funeralnews",
      },
    },
    {
      $lookup: {
        from: "News", // news collection name
        localField: "newsId",
        foreignField: "_id",
        as: "news",
      },
    },
    {
      $match: {
        $or: [
          { "news.isActive": true },
          { "events.isActive": true },
          { "funeralnews.isActive": true },
        ],
      },
    },

    {
      $group: {
        _id: null,
        myCount: {
          $sum: 1,
        },
        root: {
          $push: "$$ROOT",
        },
      },
    },
    {
      $unwind: {
        path: "$root",
      },
    },
    {
      $addFields: {
        "root.total": "$myCount",
      },
    },
    {
      $replaceRoot: {
        newRoot: "$root",
      },
    },

    {
      $sort: {
        creationDate: -1,
      },
    },
  ])
  .skip(page * limit)
  .limit(limit);