How to query document by nested relations?

I have Folder, Video and Access collections in my MongoDB database. Folder can have few Video(one-to-many) and Video can have few Access(one-to-many). I want to find Folders where Videos have specific Access in it.

I used $elemMatch like Folder.find({videos: {$elemMatch: {access: {$in: <AccessInstance>}}}}) and then also tried following technic Folder.find({"videos.access": {$in: <AccessInstance>}}) but neither of them worked for me.

Below is my model structure and relations:

@Schema()
export class Folder {
    _id: string;
 
  @Prop({ type: [mongoose.Schema.Types.ObjectId], ref: 'Video' })
  videos: Video[];
 
  // other properties..

}

@Schema()
export class Video{
    _id: string;
 
  @Prop({ type: [mongoose.Schema.Types.ObjectId], ref: 'Access' })
  access: Access[];
 
  // other properties..

}

@Schema()
export class Access{
    _id: string;
 
  // other properties..

}

What is the solution of this kind of task in Mongoose ?

You have some problems with your Schemas. You are defining the _id fields as _id: string; in all 3 Schemas but then referencing them as ObjectId such as { type: [mongoose.Schema.Types.ObjectId], ref: 'Video' }. These are not comparable without a workaround.

Since you seem to be using Nest.js with mongoose my advice would be to remove the _id field definition from each of your Schemas. Unless you want to define the _id fields as anything other than an ObjectId of course but I presume you don't. Mongoose will automatically define _id for you as an ObjectId so you can just delete those lines.

With that change in place in order to find a Folder that has Videos with specific Access in it you would need to use aggregate to do a $lookup of the videos array. That will then allow you to search the videos.access array for a match. Without that $lookup the access array has no context inside each videos array element. In other words, the access array doesn't exist yet in each Folder document.

Given an Access document that has the following _id value:

const access = { _id: ObjectId("650ecb80507561a33ce927a5") }

Your aggregate will be:

Folder.aggregate([
  {
    "$lookup": {
      "from": "videos",
      "localField": "videos",
      "foreignField": "_id",
      "as": "videos"
    }
  },
  {
    "$match": {
      "videos.access": ObjectId("650ecb80507561a33ce927a5")
    }
  }
])

See HERE for a working example.