Joining between two collections using array of children

Question

I kind of new in Mongodb and having a hard time to design my database. Basically I have 2 collection, useraccount and domainorganisation. Below is sample of document for each collection:

domainorganisation:

{
    "_id" : ObjectId("5bf95046a1bc1b47c4fe75f2"),
    "IsActive" : true,
    "Participators" : [ 
        {
            "_id" : ObjectId("5bf95044a1bc1b47c4fe75ef"), // useraccountid
            "Role" : "Loader",
            "AssignedBy" : "CONSOLE"
        }
    ]
}

useraccount:

{
    "_id" : ObjectId("5bf95044a1bc1b47c4fe75ef"),
    "Username" : "test-1",
    "Email" : "myemail1@email.com",
    "IsSystemAdministrator" : false,
    "UserProfile" : {
        "FirstName" : "firsttest1",
        "LastName" : "lasttest1"
    }
}

I would like to join this two collection as per below:

{
    "_id" : ObjectId("5bf95046a1bc1b47c4fe75f2"),
    "IsActive" : true,
    "Participators" : [ 
        {
            "Acc" : {
                "_id" : ObjectId("5bf95044a1bc1b47c4fe75ef"),
                "Username" : "test-1",
                "Email" : "myemail1@email.com",
                "IsSystemAdministrator" : false,
                "UserProfile" : {
                        "FirstName" : "firsttest1",
                        "LastName" : "lasttest1"
                        }
        }
            "Role" : "Loader",
            "AssignedBy" : "CONSOLE"
        }
    ]
}

My current solution is handling the joining in my application logic, by querying both collection, and match the data altogether. But just wondering if I can tackle this problem in mongodb layer.

Answer 1

You can build your query like this.

    db.getCollection('domainorganisation').aggregate([
    {
        "$unwind": "$Participators"
    },
    {
        "$lookup":
        {
            "from": "useraccount",
            "localField": "Participators._id",
            "foreignField": "_id",
            "as": "Joined"
        }
    },
    {
        "$group": {
            "_id": "$_id",
            "Participators": {
                "$push": {
                    "Acc": { "$arrayElemAt": ["$Joined", 0] },
                    "Role": "$Participators.Role",
                    "AssignedBy": "$Participators.AssignedBy"
                }
            },
            "IsActive": { "$first": "$IsActive" }
        }
    }
])