MongoDB How to perform an aggregation lookup on array of an array

Question

is it possible to aggregate and join match data like so:

application (Document) -> roles (DBRef array) -> permissions (DBRef array) -> name (String)

The application has roles, roles have permissions and permissions have a name.

I have been trying to figure out how I can make an aggregation operation that would achieve this type of complex join. What I want to do is choose the application that has the role, that has the permission of a given name.

Here is the basic example documents I have:

application:

{
    "_id": {
        "$numberLong": "11"
    },
    "name": "my-module",
    "roles": [{
        "$ref": "role",
        "$id": {
            "$numberLong": "17"
        }
    }
}

role:

{
    "_id": {
        "$numberLong": "17"
    },
    "name": "AdminRole",
    "application": {
        "$ref": "application",
        "$id": {
            "$numberLong": "11"
        }
    },
    "permissions": [{
        "$ref": "permission",
        "$id": {
            "$numberLong": "46"
        }
    }, {
        "$ref": "permission",
        "$id": {
            "$numberLong": "49"
        }
    }]
}

permission:

{
    "_id": {
        "$numberLong": "46"
    },
    "name": "TestPermission1"
},
{
    "_id": {
        "$numberLong": "49"
    },
    "name": "TestPermission2"
}

I have figured out how to aggregate one level of data from the roles array:

$lookup:
{
  from: 'role',
  localField: 'roles.$id',
  foreignField: '_id',
  as: '_roles'
}

$match: /**
 * query: The query in MQL.
 */
{
    $or: [{
      "_roles": {
        "$elemMatch": {
          state: 'DISABLED'
          /* how can also look through the roles permissions (array of DBRef) for permission data? */
        }
      },
      /* other checks */
    }]
}

Any help regarding this issue is appreciated!

Thanks!

Are you asking how to add second lookup stage to the pipeline? It's not quite clear what's the challenge you are facing. It seems you did a good job with first lookup. May be add some examples of the documents you are trying to join. — Alex Blex
– Alex Blex, Commented Sep 21, 2020 at 22:23
Hi Alex, thanks for the response. I have updated the main post with the sample documents I have. What I want to do is choose the application that has the role, that has the permission of a given name. — Ben Dol
– Ben Dol, Commented Sep 21, 2020 at 22:36
hmmm, are you sure the role has a reference back to the application, and the role belongs to no more than 1 application? You just have cross-references which makes it a bit less clear — Alex Blex
– Alex Blex, Commented Sep 21, 2020 at 22:49

Alex Blex · Accepted Answer · 2020-09-21 23:37:14Z

1

With this data structure you'd better search for permissions and lookup for roles and the apps after that. This way you can benefit from indexed name in permissions collection.

Something like this:

db.permission.aggregate([
  {
    "$match": {
      name: "TestPermission1"
    }
  },
  {
    "$lookup": {
      "from": "role",
      "localField": "_id",
      "foreignField": "permissions.$id",
      "as": "permissions"
    }
  },
  {
    "$lookup": {
      "from": "application",
      "localField": "permissions.application.$id",
      "foreignField": "_id",
      "as": "app"
    }
  },
  {
    "$unwind": "$app"
  },
  {
    "$replaceRoot": {
      "newRoot": "$app"
    }
  }
])

As a side note you don't have to use DBRefs, which can make it a bit simpler to use.

edited Sep 21, 2020 at 23:37

answered Sep 21, 2020 at 23:06

Alex Blex

37.3k7 gold badges53 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ben Dol Over a year ago

Thanks a lot, I didn't think about doing it from the permissions (reversed). Worked like a charm.

Ben Dol Over a year ago

Hey Alex, I had a question. Do you know if there's a way for me to do this aggregation from the application document, rather than the permission document?

Alex Blex Over a year ago

The same way. 2 lookups one after other. Just remember that you make lookup for each document from the previous stage in the pipeline, so starting from application collection you will select all roles for all applications, then all permissions for all roles * applications, and only then filter by name. It's waste of resources to my eyes, unless you have really small collections and won't notice the performance drop.

Collectives™ on Stack Overflow

MongoDB How to perform an aggregation lookup on array of an array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related