MongoDB: Reference another document with additional info attached

Question

I have a situation where I have a type of group document. I want to have a list field that contains a reference Id to the users in the group. I need to however indicate which users have admin access. Should I have two lists, one of regular users and one of admins, or have a custom document that I embed a list of that just has the reference Id and a bool value? This is basically a many to many, with both documents have a list of reference Ids to the other documents. I'm just not sure how to include this other value.

If it makes any difference I'm using Python/Mongoengine to access the MongoDB

Answer 1

There are various ways of modelling your requirement in the current form. I'll try to show you one such way and uses $lookup. You should try with two separate collections one for each groups and users like below.

One other option will be to use $DBRef which will eagerly load all the users in the group when you fetch group collection. This option will depend on python driver and I'm sure driver supports that.

Groups Document

{
    "_id": ObjectId("5857e7d5aceaaa5d2254aea2"),
    "name": "newGroup",
    "usersId": ["user1", "user2"]
}

Users Document

{ "_id" : "user1", "isAdmin" : true }
{ "_id" : "user2" }

Get All Users in a group

db.groups.aggregate({
    $unwind: '$usersId'
}, {
    $lookup: {
        from: "users",
        localField: "usersId",
        foreignField: "_id",
        as: "group_users"
    }
})

Response

{
    "_id": ObjectId("5857e7d5aceaaa5d2254aea2"),
    "name": "newGroup",
    "usersId": "user1",
    "group_users": [{
        "_id": "user1",
        "isAdmin": true
    }]
} {
    "_id": ObjectId("5857e7d5aceaaa5d2254aea2"),
    "name": "newGroup",
    "usersId": "user2",
    "group_users": [{
        "_id": "user2"
    }]
}

Get All Admin Users in a group

db.groups.aggregate({
    $unwind: '$usersId'
}, {
    $lookup: {
        from: "users",
        localField: "usersId",
        foreignField: "_id",
        as: "group_users"
    }
}, {
    $match: {
        "group_users.isAdmin": {
            $exists: true
        }
    }
})

Response

{
    "_id": ObjectId("5857e7d5aceaaa5d2254aea2"),
    "name": "newGroup",
    "usersId": "user1",
    "group_users": [{
        "_id": "user1",
        "isAdmin": true
    }]
}

Based on comment:

The is admin is admin for the group, so I can't store it in the users table. This is a many to many relationship.

I think you should include both the regular users list and admin users list. This will use the indexes you add and will make your read queries really straight forward.

Groups Document

{ "_id" : "newGroup", "userIds" : [ "user1" ], "adminIds" : [ "user2" ] }

Users Document

{ "_id" : "user1", "groupIds" : [ "newGroup" ] } -- regular user in newGroup
{ "_id" : "user2", "groupIds" : [ "newGroup" ] } -- admin user in newGroup.
{ "_id" : "user3", "groupIds" : [ ] }

Get All Regular Users

db.groups.aggregate({
    $unwind: '$userIds'
}, {
    $lookup: {
        from: "users",
        localField: "userIds",
        foreignField: "_id",
        as: "group_users"
    }
})

Get All Admin Users

db.groups.aggregate({
    $unwind: '$adminIds'
}, {
    $lookup: {
        from: "users",
        localField: "adminIds",
        foreignField: "_id",
        as: "group_users"
    }
})