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

Answer 2

As mentioned by Santiago Squarzon in his helpful answer Object Graphs (resulted from cmdlets along with ConvertFrom-Json) could be quite difficult to handle. That's why I have been busy for the last months to create some Object Graph Tools that might ease some common use cases as this one.
To install the ObjectGraphTools module:

Install-Module -Name ObjectGraphTools

Underneath the cmdlets is a [PSNode] class that gives you access to the nodes in the object graph. E.g. to retrieve all the leaf nodes with the name "name":

$Object = $JsonData | ConvertFrom-Json
$NameNodes = $Object | Get-ChildNode -Recurse -Include 'Name' -Leaf
$NameNodes

PathName                              Name Depth Value
--------                              ---- ----- -----
.Team1.'John Smith'.employees[0].name name     5 John Doe
.Team1.'John Smith'.employees[1].name name     5 Jane Vincent
.Team1.'Jane Smith'.employees[0].name name     5 John Bylaw
.Team1.'Jane Smith'.employees[1].name name     5 Jane Tormel
.Team2.'Bob Smith'.employees[0].name  name     5 Bob Doe
.Team2.'Bob Smith'.employees[1].name  name     5 Margareth Smith
.Team2.'Mary Smith'.employees[0].name name     5 Henry Bylaw
.Team2.'Mary Smith'.employees[1].name name     5 Eric Tormel

The PathName property holds the path to the specific property (e.g.: $Object.Team1.'John Smith'.employees) in the object graph.

• Type $EmployeeNodes | Get-Member to show more members of the [PSNode] class.
• For help on the cmdlets as Get-ChildNode type Get-ChildNode -? or refer to the online documents.

To get the specific $NameNode with the value property name 'Henry Bylaw':

$HenryBylawNode = $NameNodes | Where-Object Value -eq 'Henry Bylaw'
$HenryBylawNode

PathName                              Name Depth Value
--------                              ---- ----- -----
.Team2.'Mary Smith'.employees[0].name name     5 Henry Bylaw

To get the Position, Manager and Team:

 $HenryBylawNode.ParentNode.GetChildNode('Position').Value
 Clerk     

 $HenryBylawNode.ParentNode.Parentnode.Name
 Mary Smith

 $HenryBylawNode.ParentNode.Parentnode.Parentnode.Name
 Team2

Putting it together:

$JsonData | ConvertFrom-Json |
    Get-ChildNode -Recurse -Include 'Name' -Leaf |
    Where-Object Value -eq 'Henry Bylaw' |
    ForEach-Object {
        [pscustomobject]@{
            Employee = $_.Value
            Position = $_.ParentNode.GetChildNode('Position').Value
            Team     = $_.ParentNode.ParentNode.Parentnode.Parentnode.Name
            Manager  = $_.ParentNode.ParentNode.Parentnode.Name
        }
    }

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

---

Update 2024-03-08
Based on the new published ObjectGraphTools version that includes a full Extended Dot Notation (Xdn) implementation, you might use the following simplified syntax:

$Name = 'Eric Tormel'
$Json | ConvertFrom-Json | Get-Node ~Name="$Name" | ForEach-Object {
    [PSCustomObject]@{
        Employee = $_.Value
        Position = $_.GetNode('....Position').Value
        Team     = $_.GetNode('.....').Name
        Manager  = $_.GetNode('....').Name
    }
}