Iterating through JSON File PowerShell

问题:

I am trying to loop through the below JSON in Powershell.

Without specifically naming the top tags (e.g. 17443, 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?

Any help appreciated

    {
   "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 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
        }

     }  

回答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.

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-ObjectMembers {
    [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-ObjectMembers | foreach {
    $_.Value | Get-ObjectMembers | 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

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

回答2:

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 ' ' }