I have two collections Clients,Forms
Clients schema has following record
{
"_id" : ObjectId("5b0bd79adcbf901ee404d8c0"),
"Name" : "Danielle",
"Email" : "[email protected]",
"Projects" : [{
"_id" : ObjectId("5b1e6f3410ef671cf82404be"),
"Name" : "test",
"Description" : "ttet",
"Forms" : [
ObjectId("5b03ff291c70c513bc9dbfa8"),
ObjectId("5b16238f30491d1c643f7f28"),
ObjectId("5afc23f3382646009c5210ab"),
],
"IsActive" : true
}, {
"_id" : ObjectId("5b03ffc11c70c513bc9dbfb1"),
"Name" : "apadei ief",
"Description" : "ttasdadet",
"Forms" : [
ObjectId("5b03ff291c70c513bc9dbfa8"),
ObjectId("5b16238f30491d1c643f7f28")
],
"IsActive" : true
}, {
// array of projects
}
],
"IsDeleted" : false,
}
Forms schema has following record
{
"_id" : ObjectId("5b03ff291c70c513bc9dbfa8"),
"Name" : "Employee Information",
"Description" : "",
"IsActive" : true
},
{
"_id" : ObjectId("5b16238f30491d1c643f7f28"),
"Name" : "test form",
"Description" : "",
"IsActive" : true
},
{
"_id" : ObjectId("5afc23f3382646009c5210ab"),
"Name" : "Android test",
"Description" : "",
"IsActive" : true
},
{
"_id" : ObjectId("5a6304ffc3c3f119fc0e60c8"),
"Name" : "feedback form",
"Description" : "",
"IsActive" : true
}
I want output be like as below
{
"_id" : ObjectId("5b0bd79adcbf901ee404d8c0"),
"Name" : "Danielle",
"Email" : "[email protected]",
"Projects" : [{
"_id" : ObjectId("5b1e6f3410ef671cf82404be"),
"Name" : "test",
"Description" : "ttet",
"Forms" : [{
"_id" : ObjectId("5b03ff291c70c513bc9dbfa8"),
"Name" : "Employee Information",
"Description" : "",
"IsActive" : true
}, {
"_id" : ObjectId("5b16238f30491d1c643f7f28"),
"Name" : "test form",
"Description" : "",
"IsActive" : true
}, {
"_id" : ObjectId("5afc23f3382646009c5210ab"),
"Name" : "Android test",
"Description" : "",
"IsActive" : true
}
],
"IsActive" : true
}, {
"_id" : ObjectId("5b03ffc11c70c513bc9dbfb1"),
"Name" : "apadei ief",
"Description" : "ttasdadet",
"Forms" : [{
"_id" : ObjectId("5b03ff291c70c513bc9dbfa8"),
"Name" : "Employee Information",
"Description" : "",
"IsActive" : true
}, {
"_id" : ObjectId("5b16238f30491d1c643f7f28"),
"Name" : "test form",
"Description" : "",
"IsActive" : true
}
],
"IsActive" : true
}, {
// array of projects
}
],
"IsDeleted" : false
}
As per output i want forms should be come from Forms collections.
For this I am doing aggregation as below,
db.Clients.aggregate([{
$match : {
_id : ObjectId("5a8528ed0290f7eca89e9a5f"),
IsDeleted : false
}
}, {
$addFields : {
"Forms" : {
$map : {
input : {
$map : {
input : "$Projects",
in : {
$arrayElemAt : [{
$objectToArray : "$$this"
}, 1]
},
}
},
in : "$$this.v"
}
}
}
}, {
$lookup : {
from : "Forms",
localField : "Projects.Forms",
foreignField : "_id",
as : "Forms"
}
}, {
$addFields : {
"Forms" : {
$arrayElemAt : ["$Forms", 0]
}
}
}
])
but it give me wrong output,it returns only one Form from one project.I want each Forms from each Projects.
Your answer is working fine for me, but what if i want to make filter within the $map for
$and: [{
$eq: ["$Projects.IsActive", true]
}, {
$eq: ["$Projects.IsDeleted", false]
}]