How to use $and for the same child schemas

Question

I'm working with Node, Mongoose and MongoDB.

I have a user, that can have multiple associations with clubs. And I need to return all users that has a valid association for 'Club A'

So, I have:

@Schema()
export class User {
  _id: string;

@Prop({
    type: [ClubAssociationSchema],
  })
  associations: ClubAssociation[] = [];
}

And the association schema is:

@Schema()
export class ClubAssociation {
  _id: string;

  @Prop({
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Club',
    required: true,
  })
  club: Club;

  @Prop({ required: true, default: false })
  valid: boolean;

  @Prop({ required: true })
  number: string;
}

In order to return all items with valid associations in Club A I do:

const items = await this.model
      .find(
         {
              $and: ['associations.club': xxxxx, 'associations.valid': true],
})

This returns all users that has ANY association with valid: true, AND an association with Club A, even if it is valid: false. So it is checking for users that has associations with Club A and that have valid : true association, but it doesn't matter if the valid: true is for Club A or for any other club.

Answer 1

You can use the $elemMatch operator to match documents that contain an array element that matches multiple conditions on the same nested field.

F.e:

const items = await this.model.find({
  associations: {
    $elemMatch: {
      club: xxxxx,
      valid: true,
    },
  },
});

This query will match users that have at least one association that satisfies both conditions