PHP Nested JSON from flat JSON

Question

I have a database query that provides me the output of some employee data. I want to use this data to pass to a plugin that generates an org chart. There are a few fields in the JSON object that I am pulling down which are:

FirstName
LastName
EmployeeID
ManagerEmployeeID
Manager Name

The data is returned as flat JSON object with no nesting or corellation between employees and their managers in the hierarchy.

Since I am unable to change the output of the source data (the database query), I am trying to figure out a way to nest the data so that the JSON output becomes a nested output.

My goal is to take this array and nest it based on the ManagerID and EmployeeID so I can make a tree hierarchy.

Example Data:

•   Tom Jones
   o    Alice Wong
   o    Tommy J.
•   Billy Bob
   o    Rik A.
     ♣  Bob Small
     ♣  Small Jones
   o    Eric C.

My flat data example:

    {
        "FirstName": "Tom"
        "LastName": "Jones"
        "EmployeeID": "123"
        "ManagerEmployeeID": ""
        "Manager Name": ""
    },
    {
        "FirstName": "Alice"
        "LastName": "Wong"
        "EmployeeID": "456"
        "ManagerEmployeeID": "123"
        "Manager Name": "Tom Jones"
    },
    {
        "FirstName": "Tommy"
        "LastName": "J."
        "EmployeeID": "654"
        "ManagerEmployeeID": "123"
        "Manager Name": "Tom Jones"
    },
    {
        "FirstName": "Billy"
        "LastName": "Bob"
        "EmployeeID": "777"
        "ManagerEmployeeID": ""
        "Manager Name": ""
    },
    {
        "FirstName": "Rik"
        "LastName": "A."
        "EmployeeID": "622"
        "ManagerEmployeeID": "777"
        "Manager Name": "Billy Bob"
    },
    {
        "FirstName": "Bob"
        "LastName": "Small"
        "EmployeeID": "111"
        "ManagerEmployeeID": "622"
        "Manager Name": "Rik A."
    },
    {
        "FirstName": "Small"
        "LastName": "Jones"
        "EmployeeID": "098"
        "ManagerEmployeeID": "622"
        "Manager Name": "Rik A"
    },
    {
        "FirstName": "Eric"
        "LastName": "C."
        "EmployeeID": "222"
        "ManagerEmployeeID": "777"
        "Manager Name": "Billy Bob"
    }

Example Desired Output:

[
  {
    "FirstName": "Tom",
    "LastName": "Jones",
    "EmployeeID": "123",
    "ManagerEmployeeID": "",
    "Manager Name": "",
    "employees": [
      {
        "FirstName": "Alice",
        "LastName": "Wong",
        "EmployeeID": "456",
        "ManagerEmployeeID": "123",
        "Manager Name": "Tom Jones"
      },
      {
        "FirstName": "Tommy",
        "LastName": "J.",
        "EmployeeID": "654",
        "ManagerEmployeeID": "123",
        "Manager Name": "Tom Jones"
      }
    ]
  },
  {
    "FirstName": "Billy",
    "LastName": "Bob",
    "EmployeeID": "777",
    "ManagerEmployeeID": "",
    "Manager Name": "",
    "employees": [
      {
        "FirstName": "Rik",
        "LastName": "A.",
        "EmployeeID": "622",
        "ManagerEmployeeID": "777",
        "Manager Name": "Billy Bob",
        "employees": [
          {
            "FirstName": "Bob",
            "LastName": "Small",
            "EmployeeID": "111",
            "ManagerEmployeeID": "622",
            "Manager Name": "Rik A."
          },
          {
            "FirstName": "Small",
            "LastName": "Jones",
            "EmployeeID": "098",
            "ManagerEmployeeID": "622",
            "Manager Name": "Rik A"
          }
        ]
      },
      {
        "FirstName": "Eric",
        "LastName": "C.",
        "EmployeeID": "222",
        "ManagerEmployeeID": "777",
        "Manager Name": "Billy Bob"
      }
    ]
  }
]

Esentially I am trying to create a nested JSON output from a flat object using the EmployeeID and ManagerEmployeeID as the links between the two.

What is the best way to solve something like this with PHP?

Bounty Update:

Here is a test case of the issue: https://eval.in/private/4b0635c6e7b059

You will see that the very last record with the name of Issue Here does not show up in the result set. This has a managerID that matches the root node and should be within "Tom Jones's" employees array.

PHP Nested JSON from flat JSON

4 Answers