In PowerShell, how to get the name of a JSON object a data pair is in

Question

Given the following JSON:

{
  "Team1": {
    "John Smith": {
      "position": "IT Manager",
      "employees": [
        {
          "name": "John Doe",
          "position": "Programmer"
        },
        {
          "name": "Jane Vincent",
          "position": "Developer"
        }
      ]
    },
    "Jane Smith": {
      "position": "Payroll Manager",
      "employees": [
        {
          "name": "John Bylaw",
          "position": "Clerk"
        },
        {
          "name": "Jane Tormel",
          "position": "accountant"
        }
      ]
    }
  },
  "Team2": {
    "Bob Smith": {
      "position": "IT Manager",
      "employees": [
        {
          "name": "Bob Doe",
          "position": "Programmer"
        },
        {
          "name": "Margareth Smith",
          "position": "Developer"
        }
      ]
    },
    "Mary Smith": {
      "position": "Payroll Manager",
      "employees": [
        {
          "name": "Henry Bylaw",
          "position": "Clerk"
        },
        {
          "name": "Eric Tormel",
          "position": "accountant"
        }
      ]
    }
  }
}

I want to do a search for an employee name and return the Team he/she is in and his/her manager. For example for Henry Bylaw, I want to say his manager is Mary Smith and they are on Team #2. Obviously, there are more than 2 team and more managers than this sample.

I put the JSON in a variable:

$jsonData = '{
    "Team1": {
        "John Smith" : {
.... Too long to post here but see above.
        }
    }   
}

and do this:

# Convert JSON string to PowerShell object
$data = $jsonData | ConvertFrom-Json

# Function to find What team is on
function FindTeam($employeeName) {
    $team1 = $data.Team1 | Where-Object { $_.employees | Where-Object { $_.name -eq $employeeName } }
    $team2 = $data.Team2 | Where-Object { $_.employees | Where-Object { $_.name -eq $employeeName } }

    if ($team1) {
        return "Team #1"
    } elseif ($team2) {
        return "Team #2"
    } else {
        return "Not found on any teams"
    }
}

# Call the function
$employeeName = "Jane Vincent"
$result = FindTeam $employeeName
Write-Output "$employeeName is on $result"

which results in:

Jane Vincent is on Not found on any teams

As you can see this does not work and does not scale up to hundreds of teams. No, I cannot change the structure of the JSON. How can I do this?

Answer 1

This is a really hard Json to work with, I'm unsure how to explain the logic other than pointing out that .PSObject.Properties can be used to reflect on the object to get its properties (the names and values).

$json = Get-Content .\test.json -Raw | ConvertFrom-Json
$target = 'Henry Bylaw'

foreach ($team in $json.PSObject.Properties) {
    foreach ($manager in $team.Value.PSObject.Properties) {
        foreach ($employee in $manager.Value.employees) {
            if ($employee.name -eq $target) {
                # `return` is used here to exit the loop,
                # if we found `$target` there is no need to keep searching
                return [pscustomobject]@{
                    Emplyee  = $employee.name
                    Position = $employee.position
                    Team     = $team.name
                    Manager  = $manager.name
                }
            }
        }
    }
}

The output you can expect from above code is an object that has all details of that employee ($target):

Emplyee     Position Team  Manager
-------     -------- ----  -------
Henry Bylaw Clerk    Team2 Mary Smith