Question 331584 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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 $NameMode 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search