Exporting nested JSON output to CSV file

Question

I am trying to find a way to export nested JSON data into columns in a CSV file.

This is the JSON output for one of the checks we have in the 3rd party solution.

{
        "url": "***",
        "id": 46092,
        "guid": "a200efc4-2b05-422a-8785-ca5868aa7c1d",
        "name": "***",
        "check_type": "FprXnet",
        "check_type_name": "Real Browser, Chrome",
        "check_type_api": "browser",
        "enabled": true,
        "location": "Finland, Helsinki",
        "country_code": "FI",
        "sla_percent_current_month": 99.44116132753345,
        "timestamp_utc": "2023-07-31T13:45:03.563",
        "severity": "I",
        "value": 37106,
        "unit": "ms",
        "target_sla": null,
        "check_symbol": "N7_M13522_C46092_FPR_20190619_141926_713",
        "threshold_w": null,
        "threshold_w_dynamic": null,
        "threshold_e": null,
        "threshold_e_dynamic": null,
        "threshold_lo_w": null,
        "threshold_lo_w_dynamic": null,
        "threshold_lo_e": null,
        "threshold_lo_e_dynamic": null,
        "scheduled_inclusion": null,
        "scheduled_exclusion": "mon-sun : 01:00-01:15;",
        "interval_seconds": 600,
        "last_result_details": {
            "message": "10 steps, 10 pages, 296 urls, 185350/46334226 sent/received bytes",
            "attempts": 1,
            "result_code": 0
        },
        "tags": {
            "24/7 procedure": [
                "24/7"
            ],
            "Country": [
                "Finland"
            ],
            "Environment": [
                "Prod"
            ],
            "ITSystemCode": [
                "***"
            ]
        }
    },

This is how the CSV is being exported:

What I need to do is to add additional columns:

("24/7 procedure", "Country", "Environment" and "ITSystemCode")

In a CSV file for the following nested information.

"tags": {
            "24/7 procedure": [
                "24/7"
            ],
            "Country": [
                "Finland"
            ],
            "Environment": [
                "Prod"
            ],
            "ITSystemCode": [
                "***"
            ]
        }

This is the current script I got so far:

$response = Invoke-RestMethod 'https://***.***.com/v3/checks?enabled=true&auth_ticket=***' -Method 'GET'

$date = Get-Date -Format "MM-dd-yyyy-HH-mm"

$response | Select-Object -Property name,location,id,tags,timestamp_utc | Export-Csv -Path "Checks_$date.csv" -NoTypeInformation -Delimiter ";"

I am trying to export the CSV file so it includes the columns as follows:

name,location,id,tags("24/7 procedure", "Country", "Environment", "ITSystemCode"),timestamp_utc

Tried to follow this, but I couldn't get it to work/don't really understand this as I am transitioning from Python to PowerShell at the moment.

Answer 1

This would be close to what you're looking for, basically you create an array of the selectable properties and within in you include calculated properties for the nested properties in .tags:

$properties = @(
    'name'
    'location'
    'id'
    @{ N = '24/7 procedure'; E = { $_.tags.'24/7 procedure' } }
    @{ N = 'Country'; E = { $_.tags.Country } }
    @{ N = 'Environment'; E = { $_.tags.Environment } }
    @{ N = 'ITSystemCode'; E = { $_.tags.ITSystemCode } }
    'timestamp_utc'
)

$date = Get-Date -Format 'MM-dd-yyyy-HH-mm'
$response | Select-Object $properties |
    Export-Csv "Checks_$date.csv" -NoTypeInformation -Delimiter ';'

The resulting Csv for this example would look like:

"name";"location";"id";"24/7 procedure";"Country";"Environment";"ITSystemCode";"timestamp_utc"
"***";"Finland, Helsinki";"46092";"24/7";"Finland";"Prod";"***";"7/31/2023 1:45:03 PM"

If the Select-Object statement with calculated properties is too confusing for you, you might find it easier by just creating a new object output of your $response, for this see Everything you wanted to know about PSCustomObject.

$response | ForEach-Object {
    [pscustomobject]@{
        'name'           = $_.name
        'location'       = $_.location
        'id'             = $_.id
        '24/7 procedure' = $_.tags.'24/7 procedure' 
        'Country'        = $_.tags.Country
        'Environment'    = $_.tags.Environment
        'ITSystemCode'   = $_.tags.ITSystemCode
        'timestamp_utc'  = $_.timestamp_utc
    }
} | Export-Csv "Checks_$date.csv" -NoTypeInformation -Delimiter ';'