Question 398170 in Mongodb Question posted in
The official documentation can be found here.

MONGODB – how to not take null value in left join

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

    I should have only one line like this

    enter image description here


  2. 0

    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, the preserveNullAndEmptyArrays option will set the deconstruct field to null. Pretty sure you don’t want set it as false 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).

    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": {
        $ifNull: [
          "$N3.Code",
          "$N1.Code"
        ]
      },
      "N3_Name": {
        $ifNull: [
          "$N3.Name",
          "$N1.Name"
        ]
      },
      "N4_Code": "$N4.Code",
      "N4_Name": "$N4.Name"
    }
  }
])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search