I'm striving to get some aggregated data from Mongo DB. I have the following collections:
Restaurants:
{
"_id" : ObjectId("5e0ff6d424f9fc12bc3d9464"),
"name" : "Pizzaria Don Juan",
"active" : true,
"users" : [
{
"_id" : ObjectId("5e10fc2adc147a373c312144")
},
{
"_id" : ObjectId("5e11ff8003eb832ef84342a6")
}
],
"socialMedias" : [
{
"_id" : ObjectId("5e1008943330ad05d4e1867c"),
"url" : "https://instagram/jetpizzas"
},
{
"_id" : ObjectId("5e10089a3330ad05d4e1867d"),
"url" : "https://facebook.com/jetpizzas"
}
],
"branches" : [
{
"name" : "Teste"
},
{
"name" : "Teste 2"
}
],
"sections" : [
{
"name" : "Bebidas"
}
],
"__v" : 0
}
{
"_id" : ObjectId("5e0ffd23991918424c8d7c3b"),
"name" : "Pizza Ruth",
"active" : true,
"users" : [ ],
"socialMedias" : [ ],
"branches" : [ ],
"sections" : [ ],
"__v" : 0
}
{
"_id" : ObjectId("5e0ffd3d991918424c8d7c3c"),
"name" : "Feijão de Corda",
"active" : true,
"users" : [ ],
"socialMedias" : [ ],
"branches" : [ ],
"sections" : [ ],
"__v" : 0
}
Users
{
"_id" : ObjectId("5e10fc2adc147a373c312144"),
"isExpired" : false,
"isBlocked" : false,
"loginTentatives" : 0,
"profile" : 2,
"active" : true,
"username" : "[email protected]",
"password" : "$2a$10$xhmw83QXbMvSqmrKAUYn.O4fOxboEyVkVB0DGkSsJUOp7K4bYQkCm",
"email" : "",
"phone" : "",
"createdAt" : ISODate("2020-01-04T20:57:14.634Z"),
"__v" : 0
}
{
"_id" : ObjectId("5e11ff8003eb832ef84342a6"),
"isExpired" : false,
"isBlocked" : false,
"loginTentatives" : 0,
"profile" : 2,
"active" : true,
"username" : "[email protected]",
"password" : "$2a$10$wby3cs89jyO0HUbEiGLKye0jOB3U295zzIsu8xGJ4wnQtw5jcvSZO",
"email" : "",
"phone" : "",
"createdAt" : ISODate("2020-01-05T15:23:44.386Z"),
"__v" : 0
}
{
"_id" : ObjectId("5e11ff9c03eb832ef84342a7"),
"isExpired" : false,
"isBlocked" : false,
"loginTentatives" : 0,
"profile" : 2,
"active" : true,
"username" : "[email protected]",
"password" : "$2a$10$nEM3RxEjYbI77R9vOWUrMOGeHFDmdZqVKUNtTLuKZVLNQBQqIbew.",
"email" : "",
"phone" : "",
"createdAt" : ISODate("2020-01-05T15:24:12.456Z"),
"__v" : 0
}
Profiles
{
"_id" : ObjectId("5e0ea5f6832df0473cacacda"),
"number" : 1,
"name" : "Cliente",
"__v" : 0
}
{
"_id" : ObjectId("5e0ea5ff832df0473cacacdb"),
"number" : 2,
"name" : "Restaurante",
"__v" : 0
}
{
"_id" : ObjectId("5e0ea607832df0473cacacdc"),
"number" : 0,
"name" : "Admin",
"__v" : 0
}
And social medias:
{
"_id" : ObjectId("5e1008943330ad05d4e1867c"),
"name" : "Instagram",
"__v" : 0
}
{
"_id" : ObjectId("5e10089a3330ad05d4e1867d"),
"name" : "Facebook",
"__v" : 0
}
{
"_id" : ObjectId("5e1009043330ad05d4e1867f"),
"name" : "LinkedIn",
"__v" : 0
}
My goal is to get all objects related to the restaurant objects. With the following code:
db.restaurants.aggregate([
{ $lookup: { from: "users", localField: "users._id", foreignField: "_id", as: "foundUsers" } },
{$group: {
'_id': '$_id',
'name': { "$first": "$name" },
'active': { "$first": "$active" },
users: { $push: '$foundUsers' },
branches: { "$first": "$branches" },
sections: { "$first": "$sections" },
socialMedias: { "$first": "$socialMedias" }
}
},
{$unwind: '$users'},
{ $unset: 'users.password' },
{ $lookup: { from: "profiles", localField: "users.profile", foreignField: "number", as: "profile" } },
{ $addFields: { 'users.profile': { $arrayElemAt: ['$profile', 0] } } },
{ $unset: 'profile' },
{ $lookup: { from: "socialmedias", localField: "socialMedias._id", foreignField: "_id", as: "socialMedia" } },
{ $addFields: { 'socialMedias.name': { $arrayElemAt: ['$socialMedia.name', 0] } } },
{$group: {
'_id': '$_id',
'name': { "$first": "$name" },
'active': { "$first": "$active" },
users: { $first: '$users' },
branches: { "$first": "$branches" },
sections: { "$first": "$sections" },
socialMedias: { "$first": "$socialMedias" }
}
}
])
I´m getting this:
{
"_id" : ObjectId("5e0ffd3d991918424c8d7c3c"),
"name" : "Feijão de Corda",
"active" : true,
"users" : [ ],
"branches" : [ ],
"sections" : [ ],
"socialMedias" : [ ]
}
{
"_id" : ObjectId("5e0ffd23991918424c8d7c3b"),
"name" : "Pizza Ruth",
"active" : true,
"users" : [ ],
"branches" : [ ],
"sections" : [ ],
"socialMedias" : [ ]
}
{
"_id" : ObjectId("5e0ff6d424f9fc12bc3d9464"),
"name" : "Pizzaria Don Juan",
"active" : true,
"users" : [
{
"_id" : ObjectId("5e10fc2adc147a373c312144"),
"isExpired" : false,
"isBlocked" : false,
"loginTentatives" : 0,
"profile" : {
"_id" : ObjectId("5e0ea5ff832df0473cacacdb"),
"number" : 2,
"name" : "Restaurante",
"__v" : 0
},
"active" : true,
"username" : "[email protected]",
"email" : "",
"phone" : "",
"createdAt" : ISODate("2020-01-04T20:57:14.634Z"),
"__v" : 0
},
{
"_id" : ObjectId("5e11ff8003eb832ef84342a6"),
"isExpired" : false,
"isBlocked" : false,
"loginTentatives" : 0,
"profile" : {
"_id" : ObjectId("5e0ea5ff832df0473cacacdb"),
"number" : 2,
"name" : "Restaurante",
"__v" : 0
},
"active" : true,
"username" : "[email protected]",
"email" : "",
"phone" : "",
"createdAt" : ISODate("2020-01-05T15:23:44.386Z"),
"__v" : 0
}
],
"branches" : [
{
"name" : "Teste"
},
{
"name" : "Teste 2"
}
],
"sections" : [
{
"name" : "Bebidas"
}
],
"socialMedias" : [
{
"_id" : ObjectId("5e1008943330ad05d4e1867c"),
"url" : "https://instagram/jetpizzas",
"name" : "Instagram"
},
{
"_id" : ObjectId("5e10089a3330ad05d4e1867d"),
"url" : "https://facebook.com/jetpizzas",
"name" : "Instagram"
}
]
}
Note that the nested array socialMedias has a wrong value for the name of the social media (duplicated "Instagram" name, it should be one record for Instagram and another one for Facebook). Even if I try to unwind the socialMedias arrays from the restaurant collection, it returns only the restaurant object which has values of social medias as a result.
Any clue in how to make this right?
The way you merge $lookup results with existing array is an issue here. You cannot run:
{ $addFields: { 'socialMedias.name': { $arrayElemAt: ['$socialMedia.name', 0] } } },
because you'll always get first array element. You need to merge two arrays instead using $map , $filter and $mergeObjects:
{
$addFields: {
socialmedias: {
$map: {
input: "$socialMedias",
as: "sm",
in: {
$mergeObjects: [
"$$this",
{
$arrayElemAt: [ { $filter: { input: "$socialmedias", cond: { $eq: [ "$$sm.number", "$$this._id" ] } } }, 0 ]
}
]
}
}
}
}
}
You also need to apply this for user.profile since current solution is error prone.