Iterating through a JSON file PowerShell
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
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.
-Rawbecause otherwiseGet-Contentreturns an array of individual lines andJavaScriptSerializer.DeserializeObjectcan'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.-Encodingbecause it's wise to specify a text file's encoding when you read it andUTF-8is 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]@{ .. }`
First, we'll use ConvertFrom-Json cmdlet to convert a JSON string into a PowerShell data structure.
Then, to illustrate how to loop through the nested PowerShell data structure, we'll demonstrate it with a simplified example.
Given
$response = [PSCustomObject] @{
prediction = [PSCustomObject] @{
cat = 0.6576587659
dog = 0.3423412341
}
}
Our goal is to iterate through the key-value pairs inside prediction (i.e. cat and dog) and shorten their values to 3 decimal places.
Solve
$response.prediction | Get-Member -MemberType NoteProperty | ForEach-Object {
$key = $_.Name
[PSCustomObject]@{Key = $key; Value = "{0:N3}" -f $response.prediction.$key}
}
We first loop through all members of prediction and the then for each of them we assign a new member key and a 3-decimal place value.
Output
Key Value
--- -----
cat 0.658
dog 0.342
This task can be solved without resorting to the construction of a rocket, as described in the comments above =)
To iterate over a json file, we can refer to a variable that is a powershell object (into which we considered json)
See what $data.PsObject.Properties there are a lot of interesting things there =)
Decision:
$data = ConvertFrom-Json $json
foreach ($elem in $data.PsObject.Properties.Value)
{
Write-Host "Title:" $elem.data.3.value
Write-Host "First Name:" $elem.data.4.value
Write-Host "Surname:" $elem.data.5.value
}