Looping and searching through JSON using PowerShell

Question

I have a JSON file that has following contents:

{
  "status": "UP",
  "details": {
    "graphDBCheck": {
      "status": "UP"
    },
    "ds": {
      "status": "UP",
      "details": {
        "total": 100,
        "free": 50,
        "threshold": 30
      }
    },
    "db": {
      "status": "UP",
      "details": {
        "ADS": {
          "status": "UP",
          "details": {
            "database": "Influx",
            "hello": "Hello"
          }
        },
        "EARDS": {
          "status": "UP",
          "details": {
            "database": "Oracle",
            "hello": "Hello"
          }
        },
        "EFRDS": {
          "status": "UP",
          "details": {
            "database": "Sybase",
            "hello": "Hello"
          }
        }
      }
    }
  }
}

I need to be able to transform this into a CSV file that has each element's name as header and it's status or value as the next row. First "status" would have column name "API_Status"

For instance:

API_Status,graphDBCheck,ds,db,ADS,EARDS,EFRDS
UP,UP,UP,UP,UP,UP,UP

Challenge here is to make this dynamic so the output will always include any other element added that has "status" in it.

I tried this and it works but I need a dynamic way do to this:

$x = Invoke-RestMethod $url -Verbose:$VerbosePreference

[pscustomobject][ordered]@{
'API_Status' = $x.status
'db' = $x.details.db.status
'ds' = $x.details.diskspace.status
'ds_Total' = $x.details.ds.details.total
'ds_Free' = $x.details.ds.details.free
'graphDBCheck' = $x.details.graphDBCheck.status
'ADS' = $x.details.db.details.auroraDataSource.status
'EARDS' = $x.details.db.details.EARDS.status
'EFRDS' = $x.details.db.details.edsFirstRowsDataSource.status
}

Answer 1

In an ideal world, the json would be structured like this, as an expandable array with uniform properties.

[
  {
    "name": "API_Status",
    "status": "UP"
  },
  {
    "name": "graphDBCheck",
    "status": "UP"
  },
  {
    "name": "ds",
    "status": "UP"
  },
  {
    "name": "db",
    "status": "UP"
  },
  {
    "name": "ADS",
    "status": "UP"
  },
  {
    "name": "EARDS",
    "status": "UP"
  },
  {
    "name": "EFRDS",
    "status": "UP"
  }
]

Or as a csv:

name,status
API_Status,UP
graphDBCheck,UP
ds,UP
db,UP
ADS,UP
EARDS,UP
EFRDS,UP

There's plenty of other posts about looping through powershell properties Iterate over PSObject properties in PowerShell or looping through json properties: Iterating through a JSON file PowerShell