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]@{ .. } `

Answer 2

This question comes up a lot. In this case, we have to loop over properties twice. This is my current answer. Make the object a little easier to work with. Both the top level and the data properties become arrays of "name" and "value". You could use select-object calculated properties to present it any way you want. It seems like in JSON you more often get random properties, rather than an array of the same properties.

$a = cat file.json | convertfrom-json

$a = $a.psobject.properties | select name,value 
$a | foreach { $_.value.data = 
  $_.value.data.psobject.properties | select name,value }

$a.value.data.value

value
-----
{Mr}
{Jack}
{Cawles}
{Miss}
{Charlotte}
{Tann}
{Mr}
{John}
{Brokland}

Trying something similar with jq:

'{"prop1":1, "prop2":2, "prop3":3}' | jq to_entries | convertfrom-json

key    value
---    -----
prop1     1
prop2     2
prop3     3

Also, convertFrom-Json in Powershell 7 has an -AsHashTable parameter, that gives you keys and values properties.

$a = '{"name":"joe","address":"here"}' | ConvertFrom-Json -AsHashtable
$a

Name                           Value
----                           -----
name                           joe
address                        here

$a.keys
name
address

$a.values
joe
here

Answer 3

Here's a simple regex-based solution. Assuming that $sRawJson contains your JSON input:

$oRegex = [Regex]'(?:(?<="[345]":\{"value"\:\["))[^"]+'
$cParts = $oRegex.Matches(($sRawJson -replace '\s')) | Select-Object -ExpandProperty "Value"

Joining parts to get full names:

for ($i = 0; $i -lt $cParts.Count / 3; $i++) { $cParts[($i * 3)..($i * 3 + 2)] -join ' ' }