How to combine the results of two different queries with mongoose?

Question

I have 3 models (Project, Organization, User) with the schema show below. I am using mongo version 4.2.0 and Moongoose version 5.1.1.

Question

Given a specific user, how do I query all the projects they have access to:

• either via a direct role in the project itself

• or via a direct role in the organization that the project belongs to

A user could have a role in an Organization, but maybe not in one of its projects. Still, the query should be able to fetch these projects (the user has upper level access).

Example

Projects
[
    {    
        "_id" : ObjectId("1184f3c454b1fd6926c324fd"),
        "organizationId" : ObjectId("1284f3c454b1fd6926c324fd"),
        "roles" : [
            {
                user: ObjectId("1314f3c454b1fd6926c324fd"),
                role: 'Bar'
            }
        ]
    },
    {    
        "_id" : ObjectId("1172f3c454b1fd6926c324fd"),
        "organizationId" : ObjectId("1284f3c454b1fd6926c324fd"),
        "roles" : [
            {
                user: ObjectId("1313f4c584b1fd6926c324fd"),
                role: 'Bar'
            }
        ]
    }
]

Organizations
{    
    "_id" : ObjectId("1284f3c454b1fd6926c324fd"),
    "roles" : [
        {
            user: ObjectId("1314f3c454b1fd6926c324fd"),
            role: 'Bar'
        }
    ]
}

Users
{    
    "_id" : ObjectId("1314f3c454b1fd6926c324fd"),
    "name": "Foo"
}

Notes

• These two projects belong to the same organization.

• The user "Foo" belongs to this organization, but has direct access to only one of its projects.

• The query in question should return both projects for the user "Foo".

Should two different queries be combined?

Answer 1

Using an aggregation pipeline, you may use this :

db.Projects.aggregate([
  {
    $lookup: {
      from: "Organizations",
      localField: "organizationId",
      foreignField: "_id",
      as: "organization"
    }
  },
  {
    $match: {
      $or: [
        {
          "roles.user": varSpecificUserId
        },
        {
          "organization.roles.user": varSpecificUserId
        }
      ]
    }
  }
]);

---

Here are EXPLANATIONS from following database data :

Projects
[{
    "_id": "pro0",
    "organizationId": "org0",
    "roles": []
}, {
    "_id": "pro2",
    "organizationId": "org1",
    "roles": []
}, {
    "_id": "pro3",
    "organizationId": "org0",
    "roles": [
      {
        user: "usr2",
        role: "Foo"
      }
    ]
  }
]

Organizations
[{
    "_id": "org0",
    "roles": [
      {
        user: "usr0",
        role: "Foo"
      }
    ]
}, {
    "_id": "org1",
    "roles": [
      {
        user: "usr1",
        role: "Bar"
      }
    ]
  }
]

Users
[{
    "_id": "usr0",
    "name": "User0"
}, {
    "_id": "usr1",
    "name": "User1"
}, {
   "_id": "usr2",
   "name": "User2"
}, {
    "_id": "usr3",
    "name": "User3"
}]

• Let's call varSpecificUserId the specific user' _id.
• $lookup query an other collection using localField from Projects collection matching with a foreignField from Organizations collection.
• The result of $lookup is an array of all Organizationsthat match the organizationId if each Project. This array is stored at organisation using the as keyword of the $lookup object. Here is an ex. of query object at this step:

{
    "_id": "pro0",
    "organization": [
      {
        "_id": "org0",
        "roles": [
          {
            "role": "Foo",
            "user": "usr0"
          }
        ]
      }
    ],
    "organizationId": "org0",
    "roles": []
  },

• The organization now attached to the Project, we have all informations to perform a query :

  • either via a direct role in the project itself
  • or via a direct role in the organization that the project belongs to

• The $match step keeps Projects that have a matching user in roles array with the varSpecificUserId variable OR a matching user in organization's roles array. Here is the final query result with varSpecificUserId = "usr0":

[
  {
    "_id": "pro0",
    "organization": [
      {
        "_id": "org0",
        "roles": [
          {
            "role": "Foo",
            "user": "usr0"
          }
        ]
      }
    ],
    "organizationId": "org0",
    "roles": []
  },
  {
    "_id": "pro3",
    "organization": [
      {
        "_id": "org0",
        "roles": [
          {
            "role": "Foo",
            "user": "usr0"
          }
        ]
      }
    ],
    "organizationId": "org0",
    "roles": [
      {
        "role": "Foo",
        "user": "usr2"
      }
    ]
  }
]

You can use https://mongoplayground.net/ to play around with mongo querys or the new aggregation tool from MongoDB Compass if you alreadyhave data in DB.