Iterating through a JSON file PowerShell

Question

I am trying to loop through the below JSON file in PowerShell.

Without specifically naming the top tags (e.g. 17443 and 17444), as I do not know them in advance I cannot find a way to loop through the data.

I want to output tags 3, 4 and 5 (title, firstname, surname) for all the records.

How would I accomplish that?

{
   "17443":{
      "sid":"17443",
      "nid":"7728",
      "submitted":"1436175407",
      "data":{
         "3":{
            "value":[
               "Mr"
            ]
         },
         "4":{
            "value":[
               "Jack"
            ]
         },
         "5":{
            "value":[
               "Cawles"
            ]
         }
      } },
      "17444":{
         "sid":"17444",
         "nid":"7728",
         "submitted":"1436891400",
         "data":{
            "3":{
               "value":[
                  "Miss"
               ]
            },
            "4":{
               "value":[
                  "Charlotte"
               ]
            },
            "5":{
               "value":[
                  "Tann"
               ]
            }
         }
      },
      "17445":{
         "sid":"17445",
         "nid":"7728",
         "submitted":"1437142325",
         "data":{
            "3":{
               "value":[
                  "Mr"
               ]
            },
            "4":{
               "value":[
                  "John"
               ]
            },
            "5":{
               "value":[
                  "Brokland"
               ]
            }
         }
      }
   }

I can access the data with the code below, but I want to avoid putting in 17443, 17444, etc.

$data = ConvertFrom-Json $json

foreach ($i in $data.17443)
{
   foreach ($t in $i.data.3)
   {
      Write-Host $t.value
   }
   foreach ($t in $i.data.4)
   {
      Write-Host $t.value
   }
   foreach ($t in $i.data.5)
   {
      Write-Host $t.value
   }
}

Answer 1

PowerShell 3.0+

In PowerShell 3.0 and higher (see: Determine installed PowerShell version) you can use the ConvertFrom-Json cmdlet to convert a JSON string into a PowerShell data structure.

That's convenient and unfortunate at the same time - convenient, because it's very easy to consume JSON, unfortunate because ConvertFrom-Json gives you PSCustomObjects, and they are hard to iterate over as key-value pairs.

When you know the keys then there is nothing to iterate - you just access them directly, e.g. $result.thisKey.then.thatKey.array[1], and you're done.

But in this particular JSON, the keys seem to be dynamic/not known ahead of time, like "17443" or "17444". That means we need something that can turn a PSCustomObject into a key-value list that foreach can understand.

# helper to turn PSCustomObject into a list of key/value pairs
function Get-ObjectMember {
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory=$True, ValueFromPipeline=$True)]
        [PSCustomObject]$obj
    )
    $obj | Get-Member -MemberType NoteProperty | ForEach-Object {
        $key = $_.Name
        [PSCustomObject]@{Key = $key; Value = $obj."$key"}
    }
}

Now we can traverse the object graph and produce a list of output objects with Title, FirstName and LastName

$json = '{"17443": {"17444": {"sid": "17444","nid": "7728","submitted": "1436891400","data": {"3": {"value": ["Miss"]},"4": {"value": ["Charlotte"]},"5": {"value": ["Tann"]}}},"17445": {"sid": "17445","nid": "7728","submitted": "1437142325","data": {"3": {"value": ["Mr"]},"4": {"value": ["John"]},"5": {"value": ["Brokland"]}}},"sid": "17443","nid": "7728","submitted": "1436175407","data": {"3": {"value": ["Mr"]},"4": {"value": ["Jack"]},"5": {"value": ["Cawles"]}}}}'

$json | ConvertFrom-Json | Get-ObjectMember | foreach {
    $_.Value | Get-ObjectMember | where Key -match "^\d+$" | foreach {
        [PSCustomObject]@{
            Title = $_.value.data."3".value | select -First 1
            FirstName = $_.Value.data."4".value | select -First 1
            LastName = $_.Value.data."5".value | select -First 1
        }
    }
}

Output

Title                      FirstName                  LastName                 
-----                      ---------                  --------                 
Miss                       Charlotte                  Tann                     
Mr                         John                       Brokland                 

---

PowerShell 2.0 / Alternative approach

An alternative approach that also works for PowerShell 2.0 (which does not support some of the constructs above) would involve using the .NET JavaScriptSerializer class to handle the JSON:

Add-Type -AssemblyName System.Web.Extensions
$JS = New-Object System.Web.Script.Serialization.JavaScriptSerializer

Now we can do a very similar operation—even a bit simpler than above, because JavaScriptSerializer gives you regular Dictionaries, which are easy to iterate over as key-value pairs via the GetEnumerator() method:

$json = '{"17443": {"17444": {"sid": "17444","nid": "7728","submitted": "1436891400","data": {"3": {"value": ["Miss"]},"4": {"value": ["Charlotte"]},"5": {"value": ["Tann"]}}},"17445": {"sid": "17445","nid": "7728","submitted": "1437142325","data": {"3": {"value": ["Mr"]},"4": {"value": ["John"]},"5": {"value": ["Brokland"]}}},"sid": "17443","nid": "7728","submitted": "1436175407","data": {"3": {"value": ["Mr"]},"4": {"value": ["Jack"]},"5": {"value": ["Cawles"]}}}}'

$data = $JS.DeserializeObject($json)

$data.GetEnumerator() | foreach {
    $_.Value.GetEnumerator() | where { $_.Key -match "^\d+$" } | foreach {
        New-Object PSObject -Property @{
            Title = $_.Value.data."3".value | select -First 1
            FirstName = $_.Value.data."4".value | select -First 1
            LastName = $_.Value.data."5".value | select -First 1
        }
    }
}

The output is the same:

Title                      FirstName                  LastName                 
-----                      ---------                  --------                 
Miss                       Charlotte                  Tann                     
Mr                         John                       Brokland                 

If you have JSON larger than 4 MB, set the JavaScriptSerializer.MaxJsonLength property accordingly.

---

On reading JSON from files

If you read from a file, use Get-Content -Raw -Encoding UTF-8.

• -Raw because otherwise Get-Content returns an array of individual lines and JavaScriptSerializer.DeserializeObject can't handle that. Recent Powershell versions seem to have improved type-conversion for .NET function arguments, so it might not error out on your system, but if it does (or just to be safe), use -Raw.
• -Encoding because it's wise to specify a text file's encoding when you read it and UTF-8 is the most probable value for JSON files.

---

Notes

• When you build JSON that contains items with unpredictable keys, prefer an array structure like {items: [{key: 'A', value: 0}, {key: 'B', value: 1}]} over {'A': 0, 'B': 1}. The latter seems more intuitive, but it's both harder to generate and harder to consume.
• ConvertFrom-Json() gives you a PowerShell custom object (PSCustomObject) that reflects the data in the JSON string.
• You can loop though the properties of a custom object with Get-Member -type NoteProperty
• You can access the properties of an object dynamically using the $object."$propName" syntax, alternatively $object."$(some PS expression)".
• You can create your own custom object and initialize it with a bunch of properties with New-Object PSObject -Property @{...}, alternatively [PSCustomObject]@{ .. } `