Find Parent objects through intermediated child collection in MongoDB-CodePudding

In my Mongo database I have a list of Projects and a list of ProjectUsers any of whom potentially have different permissions on any given Project. Any Project may have multiple users and any ProjectUser may have permissions on multiple Projects. I have defined the ProjectUser and the Project as entities, with a Permissions collection as a subdocument of the Project.

The relevant parts of my schema look like this:

const ProjectUserSchema = {
    name: { type: String, required: true },
    email: { type: String, required: true }
    // and so on.
};

const UserPermissionSchema = {
    user: { type: Schema.Types.ObjectId, ref: "ProjectUser" },
    level: { type: Number, required: true }
}

const ProjectSchema = {
    name: { type: String, required: true },
    permissions: [ UserPermissionSchema ]
}

This works fine for interacting with a Project as we can quickly tell what users have which permissions, but I can't figure out what I need to do in order to query all the projects that the current user has permissions on.

A sample Project object looks like this:

{ "_id" : ObjectId("62b44bc0aaa95c60c065df60"), 
 "name" : "My super-secret plan",
  "permissions" : [ 
      { "user" : ObjectId("62b9d3691af5d2cb26e08574"), "level" : 1,  "_id" : ObjectId("62bed68c4d58c0c32c66c571") }, 
      { "user" : ObjectId("62befc5429b845a8eb552423"), "level" : 1, "_id" : ObjectId("62bf041629b845a8eb5524b5") } 
   ], 
   "__v" : 13 
}

I have tried a simple db.projects.find("permissions.user._id": ObjectId("62b9d3691af5d2cb26e08574")) and more complicated queries using aggregations like db.projects.find({ "permissions": { $elemMatch: { "field": "user", "value": ObjectId("62befc5429b845a8eb552423")}}}); but no amount of tweaking those values around seems to be working.

I'm using Mongoose for my application code, I assume that until I can get the query working in the command line there's not much point going any further but if Mongoose provides any utility methods that would help solve this problem, those would provide a very acceptable answer.

I feel like this should be a fairly standard case, but I can't find an obvious solution for my problem, or possibly I don't know the correct Mongo terminology to search for. How do I query a parent object based on a subdocument linked relationship?

CodePudding user response：

User in the permissions array is an ObjectID which refers to the document in users collection. There is no _id there. Your query should be:

db.projects.find({"permissions.user": ObjectId("62b9d3691af5d2cb26e08574")})