Hello I have a mongo code, I have write this :
db.Department.aggregate([
{
$match: {
ParentRef: null
}
},
{
$lookup: {
from: "Department",
localField: "Syscode",
foreignField: "ParentRef",
as: "N2"
}
},
{
$unwind: {
path: "$N2",
preserveNullAndEmptyArrays: true
}
},
{
$lookup: {
from: "Department",
localField: "N2.Syscode",
foreignField: "ParentRef",
as: "N3"
}
},
{
$unwind: {
path: "$N3",
preserveNullAndEmptyArrays: true
}
},
{
$lookup: {
from: "Department",
localField: "N3.Syscode",
foreignField: "ParentRef",
as: "N4"
}
},
{
$unwind: {
path: "$N4",
preserveNullAndEmptyArrays: true
}
},
{
$project: {
"N1_Code": "$Code",
"N1_Name": "$Name",
"N2_Code": "$N2.Code",
"N2_Name": "$N2.Name",
"N3_Code": "$N3.Code",
"N3_Name": "$N3.Name",
"N4_Code": "$N4.Code",
"N4_Name": "$N4.Name"
}
},
])
I want a code like this, this is the code I have translate in MONGODB
select N1.Code, N1.Name, N2.Code, N2.Name, N3.Code, N3.Name, N4.Code, N4.Name
from Department as N1
LEFT JOIN Department as N2 ON N1.Syscode = N2.ParentRef
LEFT JOIN Department as N3 ON N2.Syscode = N3.ParentRef
LEFT JOIN Department as N4 ON N3.Syscode = N4.ParentRef
WHERE N1.ParentRef IS NULL
problem I have bad result, mongodb does a join in null value how this is possible?
Exemple if N2.Syscode is null N3.Code show N1.Code and N3.Name show N1.Name
thx for help
2
Answers
Hello Yong your code is not working look https://mongoplayground.net/p/zBD6TWbs2Y1
In result I have 3 line with N3_Code and N3_Name when N2_Code and N2_Name are empty for N1_Code is S_Count![enter image description here](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%200%200'%3E%3C/svg%3E)
I should have only one line like this
MongoDB
$lookup
behaves LEFT JOIN in SQL and it returns an empty array for the joined result field. While the$unwind
stage deconstructs the array into multiple documents, thepreserveNullAndEmptyArrays
option will set the deconstruct field tonull
. Pretty sure you don’t want set it asfalse
as this will make the query to behave like JOIN.If you want the default value when the field is
null
, you should work with the$ifNull
(COALESCE in SQL).