I have a schema

const membershipsSchema = new Schema({
  spaceId: {
    type: Schema.Types.ObjectId,
    ref: 'Space',
  },
  member: {
    type: Schema.Types.ObjectId,
    ref: 'User',
  },
  ....
);

mongoose.model('Membership', membershipsSchema);

I want to use join statement like

Select * from membershipPlans as plans join User as users on plans.member=users._id
where plans.spaceId=id and users.status <> 'archived'; // id is coming from function arguments

I tried the aggregate pipeline like

const memberships = await Memberships.aggregate([
  {
    $match: {
      spaceId: id
    }
  },
  {
    $lookup: {
      from: 'User',
      localField: 'member',
      foreignField: '_id',
      as: 'users',
    },
  },
  {
    $match: {
      'users.status': {$ne: 'archived'}
    }
  },
]);

But on console.log(memberships); I am getting an empty array. If I try return Memberships.find({ spaceId: id }) it returns populated memberships of that space. But when I try

const memberships = await Memberships.aggregate([
  {
    $match: {
      spaceId: id
    }
  },
]}

It still returns an empty array. Not sure if I know how to use an aggregate pipeline.

CodePudding user response：

There are two things that you need to do:

1. Cast id to ObjectId.
2. Instead of using $match, just filter the contents of the users array using $filter.

Try this:

const memberships = await Memberships.aggregate([
  {
    $match: {
      spaceId: new mongoose.Types.ObjectId(id)
    }
  },
  {
    $lookup: {
      from: 'User',
      localField: 'member',
      foreignField: '_id',
      as: 'users',
    },
  },
  {
    $project: {
      users: {$filter: {
         input: "$users",
         as: "user",
         cond: {
           $ne: ["$$user.status", "archived"]
         }
      }}
    }
  },
]);