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"
}])
{ "_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 }
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",
}])
db.companies.insert([{
"_id": "AAA",
"companyName":"companyA"
}, {
"_id": "BBB",
"companyName":"companyB"
}, {
"_id": "CCC",
"companyName":"companyC"
}]);
db.teams.insert([{
"_id": "DDD",
"companyName":"teamD"
}, {
"_id": "EEE",
"companyName":"teamE"
}, {
"_id": "FFF",
"companyName":"teamF"
}]);
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"
}
}
}])
db.fellows.insert({
"_id": "yCaqt5nT3LQCBLj8j",
"title": "Fellow Title"
})
thanks a lot,