Exporting Nested Json Values to CSV using powershell

Question

I have been trying to loop through a list of .json config files to pull out 'name','entity_id','contact_info','sp_endpoint'. I have been trying to take the following code block, and dump it into a csv that lists each of the values in a column, and each row pertains to the file that value belongs to.

{
  "sfg_ping::qa::standard_sp_connections": [
    {
      "name": "test",
      "entity_id": "test",
      "contact_info": "test@test.com",
      "sp_endpoint": "test",
      "sso_initialization_and_signature_policy": {
        "sign_assertion_or_response": "response",
        "sp_trust_model": "anchored",
        "signing_certificate_id": "test",
        "sp_initiated": {
          "require_signed_authn_requests": true,
          "primary_verification_certificate": "test"
        }
      }

I have been using this script to try and accomplish this, but it dumps empty values, and doesnt handle a bulk list of files.

Get-ChildItem -Path "C:\Users\user\appdev\powershell-scripts\spConnections" |
ForEach-Object { 
    {(Select-String -Pattern 'name' )}
    {(Select-String -Pattern 'entity_id' )}
    {(Select-String -Pattern 'contact_info' )}
    {(Select-String -Pattern 'sp_endpoint' )}
    }| Export-Csv "C:\Users\user\appdev\powershell-scripts\export.csv"

The intended output would be something like this:

| filename | name | entityid |
|:---------|:----:| --------:|
| test     | test | test     |

Really lost here and just looking for some direction.

All these Json files have the same parent property `sfg_ping::qa::standard_sp_connections` ? Also, your code doesn't make much sense, what is `$_.disciplines.Count` and all those `discipline` properties and measurements you're showing in your code and how they related to the actual question ? — Santiago Squarzon, Sep 29 '22 at 16:30
@SantiagoSquarzon Sorry! I just realized I pasted the wrong script ive been trying into my question. It is updated. I have also tried to use convertfrom-json, but I cant get past the parent property to dump the object properties that I am looking for. — Ben S, Sep 29 '22 at 16:37

Santiago Squarzon · Accepted Answer · 2022-09-29T16:59:46.843

Seems like you can get the desired information from your Json files by following this logic, however it assumes that the Json files only have one parent Property, like sfg_ping::qa::standard_sp_connections but this would work with any Property Name as long as there is only one.

Get-ChildItem path\to\thejsonfolder -Filter *.json -PipelineVariable file | ForEach-Object {
    (Get-Content $_ -Raw | ConvertFrom-Json).PSObject.Properties.Value | Select-Object @(
        @{ N = 'FileName'; E = { $file.Name }}, 'Name', 'entity_id', 'contact_info', 'sp_endpoint'
    )
} | Export-Csv path\to\myexport.csv -NoTypeInformation

The result I get for this using 2 different Json files:

FileName   name  entity_id contact_info   sp_endpoint
--------   ----  --------- ------------   -----------
test.json  test  test      test@test.com  test
test2.json test2 test2     test2@test.com test2

If they all had the same parent Property Name then:

(Get-Content $_ -Raw | ConvertFrom-Json).PSObject.Properties.Value

Could be replaced by:

(Get-Content $_ -Raw | ConvertFrom-Json).'sfg_ping::qa::standard_sp_connections'

Exporting Nested Json Values to CSV using powershell

1 Answers1