CODEWITHSUNDEEP
arraysmongodbaggregation
Ben Dol
Ben Dol

Reputation: 427

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

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!

Upvotes: 1

Views: 173

Answers (1)

Alex Blex
Alex Blex

Reputation: 37108

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.

Upvotes: 1

Related Questions