MongoDB $lookup aggregation example

aggregation_lookup.md

MongoDB $lookup aggregation

SO link

db.votes.aggregate([{
    $lookup: {
        from: "users",
        localField: "createdBy",
        foreignField: "_id",
        as: "user"
    }
}, {
    $unwind: "$user"
}, {
    $unwind: "$user.emails"
}, {
    $sort: { "user.emails.verified": -1 }
}, {
    $group: {
        _id: "$_id",
        createdBy: { $first: "$createdBy" },
        fellowId: { $first: "$fellowId" },
        companyId: { $first: "$companyId" },
        teamId: { $first: "$teamId" },
        user: { $first: "$user" }
    }
}, {
    $lookup: {
        from: "fellows",
        localField: "fellowId",
        foreignField: "_id",
        as: "fellow"
    }
}, {
    $unwind: "$fellow"
}, {
    $lookup: {
        from: "companies",
        localField: "companyId",
        foreignField: "_id",
        as: "company"
    }
}, {
    $unwind: "$company"
}, {
    $lookup: {
        from: "teams",
        localField: "teamId",
        foreignField: "_id",
        as: "team"
    }
}, {
    $unwind: "$team"
}, {
    $project: {
        "_id": 1,
        "firstName": "$user.profile.name.first",
        "lastName": "$user.profile.name.last",
        "emailAddress": "$user.emails.address",
        "companyTitle": "$company._id",
        "teamTitle": "$team._id",
        "fellowTitle": "$fellow._id",
        "isVerified": "$user.emails.verified"
    }
}, {
    $out: "results"
}])

• Output :

{ "_id" : "5qgfddRubJ32pS48B22222222", "firstName" : "CCCCCC", "lastName" : "DDDDDD", "emailAddress" : "someuser111@example.com", "companyTitle" : "CCC", "teamTitle" : "FFF", "fellowTitle" : "yCaqt5nT3LQCBLj8j", "isVerified" : false }
{ "_id" : "5qgfddRubJ32pS48B111111", "firstName" : "AAAAAAA", "lastName" : "BBBBBB", "emailAddress" : "someuser1@example.com", "companyTitle" : "BBB", "teamTitle" : "EEE", "fellowTitle" : "yCaqt5nT3LQCBLj8j", "isVerified" : true }
{ "_id" : "5qgfddRubJ32pS48B", "firstName" : "AZAZAZAZ", "lastName" : "SDSDSDSDSD", "emailAddress" : "someuser@example.com", "companyTitle" : "AAA", "teamTitle" : "DDD", "fellowTitle" : "yCaqt5nT3LQCBLj8j", "isVerified" : true }

Sample collections

• votes collection :

db.votes.insert([{
    "_id": "5qgfddRubJ32pS48B",
    "createdBy": "HdKRfwzGriMMZgSQu",
    "companyId": "AAA",
    "teamId":"DDD",
    "fellowId": "yCaqt5nT3LQCBLj8j",
},{
    "_id": "5qgfddRubJ32pS48B111111",
    "createdBy": "HdKRfwzGriMMZgqsdqsdSQu",
    "companyId": "BBB",
    "teamId":"EEE",
    "fellowId": "yCaqt5nT3LQCBLj8j",
},{
    "_id": "5qgfddRubJ32pS48B22222222",
    "createdBy": "HdKRfwzGriMMZgqsdqsdSQu1212",
    "companyId": "CCC",
    "teamId":"FFF",
    "fellowId": "yCaqt5nT3LQCBLj8j",
}])

• companies collection :

db.companies.insert([{
    "_id": "AAA",
    "companyName":"companyA"
}, {
    "_id": "BBB",
    "companyName":"companyB"
}, {
    "_id": "CCC",
    "companyName":"companyC"
}]);

• teams collection :

db.teams.insert([{
    "_id": "DDD",
    "companyName":"teamD"
}, {
    "_id": "EEE",
    "companyName":"teamE"
}, {
    "_id": "FFF",
    "companyName":"teamF"
}]);

• users collection :

db.users.insert([{
    "_id": "HdKRfwzGriMMZgSQu",
    "emails": [{
        "address": "someuser@example.com",
        "verified": true
    }, {
        "address": "someuser1@example.com",
        "verified": false
    }],
    "profile":{
        "name":{
            "first":"AZAZAZAZ",
            "last":"SDSDSDSDSD"
        }
    }
},{
    "_id": "HdKRfwzGriMMZgqsdqsdSQu",
    "emails": [{
        "address": "someuser111@example.com",
        "verified": false
    }, {
        "address": "someuser1@example.com",
        "verified": true
    }],
    "profile":{
        "name":{
            "first":"AAAAAAA",
            "last":"BBBBBB"
        }
    }
},{
    "_id": "HdKRfwzGriMMZgqsdqsdSQu1212",
    "emails": [{
        "address": "someuser111@example.com",
        "verified": false
    }, {
        "address": "someuser1@example.com",
        "verified": false
    }],
    "profile":{
        "name":{
            "first":"CCCCCC",
            "last":"DDDDDD"
        }
    }
}])

• fellows collection :

db.fellows.insert({
    "_id": "yCaqt5nT3LQCBLj8j",
    "title": "Fellow Title"
})