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-15T15:44:55.397

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). — mklement0, Dec 15 '21 at 15:46

score 1 · Answer 2 · answered Dec 11 '21 at 11:15

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

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. — user2474226, Dec 11 '21 at 19:35

score 0 · Answer 3 · answered Dec 10 '21 at 17:53

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

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 — user2474226, Dec 10 '21 at 18:46

score 0 · Answer 4 · answered Dec 10 '21 at 18:05

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 '21 at 18:05

Kiran Reddy

2,836
2
16
20

Thanks but this doesn't get rid of the type information and it's not clear how to convert this to CSV? – user2474226 Dec 10 '21 at 18:47
It did for me...did you run the code?....exporting to csv.... `$newobj | export-csv c:\temp\mycsv` – Kiran Reddy Dec 11 '21 at 03:07
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[] – user2474226 Dec 11 '21 at 19:07
ok...there are other solutions posted so good luck. – Kiran Reddy Dec 12 '21 at 04:49

Powershell nested JSON to csv conversion

4 Answers4

Related