Powershell nested JSON to csv conversion

Question

I have a rather peculiar nested JSON where in some instances a key - value pair occurs as normal, but in others the type of the key appears in a further nesting.

{"metadata":{"systemId":"da1895","legalEntity":"A0"},"recordContent":{"positionDate":"2019-04-08 00:00:00.0","account":{"string":"G32"},"seg":{"string":"S"},"strike":{"double":4.4}}}
{"metadata":{"systemId":"45364d","legalEntity":"5G"},"recordContent":{"positionDate":"2019-04-08 00:00:00.0","account":{"string":"G81"},"seg":{"string":"S"},"strike":{"double":5.0}}}

In the example you can see metadata's fields are straightforward key-value pairs, but underneath recordContent, we have positionDate which is a straightforward key-value but "account":{"string":"G32"} and "strike":{"double":4.4} are not.

I'd like to ditch the type information and arrive at a CSV structure as follows:

systemId, legalEntity, positionDate,         account,seg,strike
da1895,   A0,          2019-04-08 00:00:00.0,G32,    S,  4.4
4536d,    5G,          2019-04-08 00:00:00.0,G81,    S,  5.0

Any ideas on how to convert such a structure to CSV using Powershell?

Here's what I tried:

$TemplateParametersFile = "c:\data\output.json"
$JsonParameters = Get-Content $TemplateParametersFile | ConvertFrom-Json

$metadatafields = $JsonParameters.metadata[0].PSObject.Properties.Name
$recordcontentfields = $JsonParameters.recordContent[0].PsObject.Properties.Name

$oData = New-Object PSObject

$metadatafields |
ForEach {
    Add-Member -InputObject $oData -NotePropertyName ($_) -NotePropertyValue $JsonParameters.metadata.($_)
}

$recordcontentfields |
ForEach {
    Add-Member -InputObject $oData -NotePropertyName ($_) -NotePropertyValue $JsonParameters.recordContent.($_)
}

This gave me:

$oData

systemId     : {da1895, 45364d}
legalEntity  : {A0, 5G}
positionDate : {2019-04-08 00:00:00.0, 2019-04-08 00:00:00.0}
account      : {@{string=G32}, @{string=G81}}
seg          : {@{string=S}, @{string=S}}
strike       : {@{double=4.4}, @{double=5.0}}

I'm a bit stuck now and the above doesn't convert to csv.

Note that other than metadata and recordContent, I've not hardcoded any fieldnames and I'd like to maintain that flexibility in case the JSON structure changes.

Thanks

mklement0 · Accepted Answer · 2021-12-15 15:44:55Z

I suggest collecting the property-name-value pairs iteratively in an ordered hashtable ([ordered] @{}), which can then be cast to [pscustomobject] to convert it to a custom object.

No property names are hard-coded in the following solution, but the object-graph structure is assumed to follow the pattern in your sample JSON, which is limited to one level of nesting - if you need to process arbitrarily nested objects, this answer may be a starting point.

Reflection (discovery of the property names and values) is performed via the intrinsic .psobject property that PowerShell makes available on all objects.

# Parse sample JSON into an array of [pscustomobject] graphs.
$fromJson = ConvertFrom-Json @'
[
  {"metadata":{"systemId":"da1895","legalEntity":"A0"},"recordContent":{"positionDate":"2019-04-08 00:00:00.0","account":{"string":"G32"},"seg":{"string":"S"},"strike":{"double":4.4}}}
  ,
  {"metadata":{"systemId":"45364d","legalEntity":"5G"},"recordContent":{"positionDate":"2019-04-08 00:00:00.0","account":{"string":"G81"},"seg":{"string":"S"},"strike":{"double":5.0}}}
]
'@

# Initialize an aux. ordered hashtable to collect the property-name-value
# pairs in.
$oht = [ordered] @{}

$fromJson | ForEach-Object {
    $oht.Clear()
    # Loop over top-level properties.
    foreach ($topLevelProp in $_.psobject.Properties) {
      # Loop over second-level properties.
      foreach ($prop in $topLevelProp.Value.psobject.Properties) {
        if ($prop.Value -is [System.Management.Automation.PSCustomObject]) {
          # A nested value: Use the value of the (presumed to be one-and-only)
          #                 property of the object stored in the value.
          $oht[$prop.Name] = $prop.Value.psobject.Properties.Value
        }
        else {
          # A non-nested value: use as-is.
          $oht[$prop.Name] = $prop.Value
        }
      }
    }
    # Construct and output a [pscustomobject] from the aux. ordered hashtble.
    [pscustomobject] $oht
  } |
      ConvertTo-Csv  # Replace this with Export-Csv to export to a file.

The above yields:

"systemId","legalEntity","positionDate","account","seg","strike"
"da1895","A0","2019-04-08 00:00:00.0","G32","S","4.4"
"45364d","5G","2019-04-08 00:00:00.0","G81","S","5"

Glad to hear it, @user2474226. I've made the nesting constraint clearer in the answer, and I've added a link to an answer that can handle arbitrary nesting (in principle, not the same use case).

iRon · Accepted Answer · 2021-12-11 11:15:45Z

1

A few years ago, I wrote a reusable Flatten-Object function for this.
The only difference is that it combines the (sub)property names with the parent property names as they might not be unique:

$JsonParameters |Flatten-Object |Format-Table

metadata.systemId metadata.legalEntity recordContent.positionDate recordContent.account.string recordContent.seg.string recordContent.strike.double
----------------- -------------------- -------------------------- ---------------------------- ------------------------ ---------------------------
da1895            A0                   2019-04-08 00:00:00.0      G32                          S                                                4.4
45364d            5G                   2019-04-08 00:00:00.0      G81                          S                                                  5

answered Dec 11, 2021 at 11:15

iRon

24.4k10 gold badges60 silver badges107 bronze badges

1 Comment

user2474226 Over a year ago

This is pretty neat, though not completely what I'm looking for as you see that the type (string or double) appears in the final column names. I'll study your function a bit more to understand it.

Hazrelle · Accepted Answer · 2021-12-10 17:53:20Z

0

Try this:

$data = ConvertFrom-Json @"
[
{"metadata":{"systemId":"da1895","legalEntity":"A0"},"recordContent":{"positionDate":"2019-04-08 00:00:00.0","account":{"string":"G32"},"seg":{"string":"S"},"strike":{"double":4.4}}},
{"metadata":{"systemId":"45364d","legalEntity":"5G"},"recordContent":{"positionDate":"2019-04-08 00:00:00.0","account":{"string":"G81"},"seg":{"string":"S"},"strike":{"double":5.0}}}
]
"@

$data | Select-Object -Property @{l="systemId"; e={$_.metadata.systemId}}, @{l="legalEntity"; e={$_.metadata.legalEntity}},
@{l="positionDate"; e={$_.recordContent.positionDate}}, @{l="account"; e={$_.recordContent.account.string}},
@{l="seg"; e={$_.recordContent.seg.string}}, @{l="strike"; e={$_.recordContent.strike.double}} | Export-Csv

answered Dec 10, 2021 at 17:53

Hazrelle

8566 silver badges9 bronze badges

1 Comment

user2474226 Over a year ago

thanks - but you're hardcoding the field names and fixing the types, which doesn't really help. i need a way to ignore the types and be able to flexibly pick up any number of fields

Kiran Reddy · Accepted Answer · 2021-12-10 18:05:29Z

0

This should work with any nested psobject.

$json = @'
{"metadata":{"systemId":"da1895","legalEntity":"A0"},"recordContent":{"positionDate":"2019-04-08 00:00:00.0","account":{"string":"G32"},"seg":{"string":"S"},"strike":{"double":4.4}}}

'@
$obj = ConvertFrom-Json $json


$obj.recordContent | gm -MemberType NoteProperty | % {
    $prop = $_.name
    if ($obj.recordContent.$prop.GetType().name -eq 'pscustomobject') {
        $obj.recordContent.$prop = $obj.recordContent.$prop.psobject.Members | where membertype -eq noteproperty | select -ExpandProperty value
    }
    $obj.metadata | add-member -MemberType NoteProperty -Name $prop -Value $obj.recordContent.$prop

}
$newobj = $obj.metadata
$newobj

answered Dec 10, 2021 at 18:05

Kiran Reddy

2,9242 gold badges18 silver badges21 bronze badges

4 Comments

user2474226 Over a year ago

Thanks but this doesn't get rid of the type information and it's not clear how to convert this to CSV?

Kiran Reddy Over a year ago

It did for me...did you run the code?....exporting to csv.... $newobj | export-csv c:\temp\mycsv

user2474226 Over a year ago

yes i ran your code - which has only 1 json line - and exported to csv and that worked as you said. but i read in 2 lines from a json file (as i have in my question) $obj = Get-Content 'output.json'|ConvertFrom-Json and then after running the rest of your code, the account, positionDate, seg and strike fields appeared as System.Object[]

Kiran Reddy Over a year ago

ok...there are other solutions posted so good luck.

Collectives™ on Stack Overflow

Powershell nested JSON to csv conversion

4 Answers 4

1 Comment

1 Comment

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related