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
}

js2010 · Accepted Answer · 2020-06-11 21:05:54Z

0

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

edited Jun 11, 2020 at 21:05

answered Jun 11, 2020 at 20:49

js2010

28.3k6 gold badges82 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Looping and searching through JSON using PowerShell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related