Lookup! When your looking down

Hi! I hope I was able to catch your eye with my click baity title.
I am honestly stuck in a problem that I cannot for the life of me solve.

I’ve attached the information in this Google Link: https://docs.google.com/document/d/1rqZwuaI2omkqFE7BOYybwNQAVaijUcTo7MhNzlmLtI8/edit?usp=sharing

In a nutshell, I have 2 collections that I want to reference then rereference again.

I have employee and manages:

2

  • Manages holds the foreign keys to employee IDs
  • I need to be able to output list of employees and their manager (show the employee even if they do not have a manager)

What I have currently done is to db.employee.aggregate then look up if the employee id matches with the manages._id.manageeID as manageeData

What I’d like to do next is from manageeData, get _id.managerID and get the employee data by comparing it to the employee table

But I just can’t seem to find the right pipeline for it & I just learned MongoDB 2 days ago, and I’m am really stuck in this problem. I hope you can help me.

Do I need to use embedding instead of referencing? Is there some sort of pipeline I should use?

Hello @Gelibellybeans, welcome to the MongoDB community forum.

Please share a sample of the expected output (as you described above)?

That said, the lookup operation output (I think, the manageeData) looks like this (for brevity I have not included all the fields, just a employee name):

{ "_id" : 1, "name" : "abc", "output" : [ ] }
{ "_id" : 2, "name" : "efg", "output" : [ ] }
{
        "_id" : 3,
        "name" : "ijk",
        "output" : [
                {
                        "_id" : {
                                "managerID" : 1,
                                "manageeID" : 3
                        }
                }
        ]
}
{
        "_id" : 4,
        "name" : "mno",
        "output" : [
                {
                        "_id" : {
                                "managerID" : 1,
                                "manageeID" : 4
                        }
                }
        ]
}
{ "_id" : 5, "name" : "xyz", "output" : [ ] }

If you expect to get the full hierarchy then it’s possible you need $graphLookup rather than regular $lookup