How to build a nested array result from self referencing table

Question

I have a db where a foreign key references my primary key, Ill like to select all rows where the foreign key is NULL and then add rows with a foreign key as nested array under their corresponding foreign key

Example:
id    name     managerId
1     Jon       NULL
2     Fin       1
3     Gabe      NULL
4     Jack      1

I want to select:

[
{
  id: 1,
  name: Jon,
  managerId: null,
  staffs: [
    {
      id: 2,
      name: Fin,
      managerId: 1,
    },
    {
      id: 4,
      name: Jack,
      managerId: 1,
    }
  ]
},
{
  id: 4,
  name: Gabe,
  managerId: null,
}
]

Answer 1

If you can retrieve the results from the database in an array, you can use a function like this to create your hierarchy:

const createHierarchy = (employeeList) => {
    const addToHierarchyList = (fk, list) => {
        return list.filter(x => x[2] == fk)
            .map(x => { return { id: x[0], name: x[1], managerId: x[2] } })
    }
    const hierarchyList = [];
    employeeList.forEach(x => {
        if (!x[2]) {
            hierarchyList.push({
                id: x[0],
                name: x[1],
                manager: x[2],
                staff: addToHierarchyList(x[0], employeeList)
            });
        }
    });
    return hierarchyList;
}