How to traverse JSON properties with Powershell

Question

I am trying to access a particular property value of a JSON object with Powershell. Unfortunately I do not know the keys of some of the parent properties within the structure, so I cannot do this in a straightforward way. Also as the JSON is not an array I am unable to access via index position.

The context is that I am querying a list of running tasks from elasticsearch and need to get the ID of the task (I know that there will only be one) so I can make subsequent calls to discover its completion status.

I've researched some querying methods but am unsure on how to apply them (PowerShell syntax is quite new to me).

The JSON response I am working with looks like this;

  "nodes" : {
    "oTUltX4IQMOUUVeiohTt8A" : {
      "name" : "H5dfFeA",
      "transport_address" : "127.0.0.1:9300",
      "host" : "127.0.0.1",
      "ip" : "127.0.0.1:9300",
      "tasks" : {
        "oTUltX4IQMOUUVeiohTt8A:124" : {
          "node" : "oTUltX4IQMOUUVeiohTt8A",
          "id" : 124,
          "type" : "direct",
          "action" : "cluster:monitor/tasks/lists[n]",
          "start_time_in_millis" : 1458585884904,
          "running_time_in_nanos" : 47402,
          "cancellable" : false,
          "parent_task_id" : "oTUltX4IQMOUUVeiohTt8A:123"
        }
      }
    }
  }
}

With this given structure, I would like to be able to access the ID property of the first 'task'.

So if I knew the prop keys it would be:

nodes.oTUltX4IQMOUUVeiohTt8A.tasks.oTUltX4IQMOUUVeiohTt8A:124.id

How can I access this value without knowing the keys beforehand?

Any help very appreciated.

Thanks Nick

Answer 1

The following code defines and uses function Get-FirstPropertyValue, which performs a recursive, depth-first search for the first property inside an object graph that has a given name and returns its value, assuming the value is non-null:

# Function that returns the value of the first property with the given 
# name found during recursive depth-first traversal of the given object.
# Note that null-valued properties are ignored.
function Get-FirstPropertyValue($obj, $propName) {
  $propNames = $obj.psobject.properties.Name
  if ($propName -in $propNames) {
    $obj.$propName
  } else {
    foreach ($iterPropName in $propNames) {
        if ($null -ne ($val = Get-FirstPropertyValue $obj.$iterPropName $propName)) {
          return $val
        }
      }
  }
}

# Input JSON
$json = @'
{
    "nodes": {
        "oTUltX4IQMOUUVeiohTt8A": {
            "name": "H5dfFeA",
            "transport_address": "127.0.0.1:9300",
            "host": "127.0.0.1",
            "ip": "127.0.0.1:9300",
            "tasks": {
                "oTUltX4IQMOUUVeiohTt8A:124": {
                    "node": "oTUltX4IQMOUUVeiohTt8A",
                    "id": 124,
                    "type": "direct",
                    "action": "cluster:monitor/tasks/lists[n]",
                    "start_time_in_millis": 1458585884904,
                    "running_time_in_nanos": 47402,
                    "cancellable": false,
                    "parent_task_id": "oTUltX4IQMOUUVeiohTt8A:123"
                }
            }
        }
    }
}
'@

# Convert the JSON to a [pscustomobject] graph with ConvertFrom-Json.
$objFromJson = $json | ConvertFrom-Json

# Using the function defined above, get the first 'tasks' object found
# during recursive depth-first traversal.
$tasks = Get-FirstPropertyValue $objFromJson 'tasks'

# Get the name of the resulting object's first property.
$propName = @($tasks.psobject.properties.Name)[0]

# Extract the .id property from the object stored in the first property.
$tasks.$propName.id

The above yields:

124

---

A more concise, but more obscure and presumably slower alternative is to convert the JSON input to XML and then use XPath to query it:

# Input JSON
$json = @'
{
    "nodes": {
        "oTUltX4IQMOUUVeiohTt8A": {
            "name": "H5dfFeA",
            "transport_address": "127.0.0.1:9300",
            "host": "127.0.0.1",
            "ip": "127.0.0.1:9300",
            "tasks": {
                "oTUltX4IQMOUUVeiohTt8A:124": {
                    "node": "oTUltX4IQMOUUVeiohTt8A",
                    "id": 124,
                    "type": "direct",
                    "action": "cluster:monitor/tasks/lists[n]",
                    "start_time_in_millis": 1458585884904,
                    "running_time_in_nanos": 47402,
                    "cancellable": false,
                    "parent_task_id": "oTUltX4IQMOUUVeiohTt8A:123"
                }
            }
        }
    }
}
'@

$parent = 'tasks'
$prop = 'id'
$propType = 'int'

$json | 
  ConvertFrom-Json |
    ConvertTo-Xml -Depth ([int]::MaxValue) | 
      Select-Xml "//Property[@Name='$parent']/*/*[@Name='$prop']/text()" |
        ForEach-Object { $_.Node.InnerText -as $propType }