Exporting Nested Json Values to CSV using powershell-CodePudding

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 collum, and each row pertains to the file that value belongs to.

{
  "sfg_ping::qa::standard_sp_connections": [
    {
      "name": "test",
      "entity_id": "test",
      "contact_info": "[email protected]",
      "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.

CodePudding user response：

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      [email protected]  test
test2.json test2 test2     [email protected] 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'