I am trying to convert json file containing multiple nested arrays to csv file using powershell

I am trying to convert json file to csv file using Powershell. The json file that I am trying to convert has multiple nested arrays. I want to expand them all. But I am able to expand only one sub-array at a time. Following is the file structure and the code I am working with:

This is a snapshot of my feed.json file:

{  
    "result": {  
    "problems": [  
      {  
        "id": "AHR157689",  
        "displayName": "YOCETJE",  
        "impact": "STRUCTURE",  
        "status": "OPEN",  
        "tagsOfEntities": [],  
        "ranked": [  
          {  
            "entityId": "843675746378564876",  
            "entityName": "HGFUTGYJDH",  
            "severityLevel": "8957685N8Y",  
          }  
        ],  
        "affectedCounts": {  
          "INFRA": 1,  
          "STRUCTURE": 0,  
          "APPLICATION": 0,   
        },  
        "recoveredCounts": {  
          "INFRA": 0,  
          "STRUCTURE": 0,  
          "APPLICATION": 0,  
        },  
        "RootCause": true  
      }  
}  
}  
}  

And below is the code I am working with:

Get-Content C:\Downloads\feed.json -Raw | ConvertFrom-Json |
    Select -Expand results | Select -Expand problem | ConvertTo-Csv |
    Out-File C:\Downloads\output.csv

With this above code I cam not getting the 'tagsofEntities' and 'ranked' data in the csv file. The column for tagsofEntities and ranked shows the value as "System.Object[]".

This is the output in output.csv fil. This all data comes in one cell.

AHR157689,"YOCETJE","STRUCTURE","OPEN","System.Object[]","System.Object[]","@{"INFRA": 1; "STRUCTURE": 0,"APPLICATION": 0}","@{"INFRA": 0;"STRUCTURE": 0;"APPLICATION": 0}","true"

  • Why are you using ConverTo-CSV and Out-File vs Export-Csv. You also have no property called 'problem' in the JSON. It's called 'problems'

    Export-Csv
    Converts objects into a series of comma-separated value (CSV) strings and saves the strings to a file.
    https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-6

    Or just natively walkgin the JSON object. JSON is a first class thing in PowerShell. As PowerShell is heavily dependant on JSON use under the covers as well.

    Your JSON is also not properly formatted, so will not work.
    Go to jsonlint.com and copy and paste you json data and hit validate and you will see it will error out with this error:


    Results

    Error: Parse error on line 12:
    ...: "8957685N8Y",    }],    "affectedCou
    ----------------------^
    Expecting 'STRING', got '}'


    Or use VSCode and the JSON linter to see your issues

    You have a bunch of trailing commas where they should not be, missing brackets, etc. Once you correct those, you can just step thru the data.
    It becomes table formated / Csv exportable by default

    Example, with corrected JSON:


    # Using varialbe squeezing to assing resutls and output to screen
    ($JSONData = @'
    {
        "result": {
            "problems": [
                {
                    "id": "AHR157689",
                    "displayName": "YOCETJE",
                    "impact": "STRUCTURE",
                    "status": "OPEN",
                    "tagsOfEntities": [],
                    "ranked": [
                        {
                            "entityId": "843675746378564876",
                            "entityName": "HGFUTGYJDH",
                            "severityLevel": "8957685N8Y"
                        }
                    ],
                    "affectedCounts": {
                        "INFRA": 1,
                        "STRUCTURE": 0,
                        "APPLICATION": 0
                    },
                    "recoveredCounts": {
                        "INFRA": 0,
                        "STRUCTURE": 0,
                        "APPLICATION": 0
                    },
                    "RootCause": true
                }
            ]
        }
    }
    '@ | ConvertFrom-Json)

    # Results

    <#
    result                    
    ------                    
    @{problems=System.Object[]}
    #>

    $JSONData.result

    # Results

    <#
    problems                                                                                                                                                   
    --------                                                                                                                                                   
    {@{id=AHR157689; displayName=YOCETJE; impact=STRUCTURE; status=OPEN; tagsOfEntities=System.Object[]; ranked=System.Object[]; affectedCounts=; recoveredCo...
    #>


    $JSONData.result.problems

    # Results

    <#
    id              : AHR157689
    displayName     : YOCETJE
    impact          : STRUCTURE
    status          : OPEN
    tagsOfEntities  : {}
    ranked          : {@{entityId=843675746378564876; entityName=HGFUTGYJDH; severityLevel=8957685N8Y}}
    affectedCounts  : @{INFRA=1; STRUCTURE=0; APPLICATION=0}
    recoveredCounts : @{INFRA=0; STRUCTURE=0; APPLICATION=0}
    RootCause       : True
    #>

    $JSONData.result.problems |
    Export-Csv -Path 'D:\Temp\RawJsonStuff.csv'

    $JSONData.result.problems |
    Select-Object -Property tagsOfEntities, ranked

    # Results

    <#
    tagsOfEntities ranked                                                                          
    -------------- ------                                                                          
    {}             {@{entityId=843675746378564876; entityName=HGFUTGYJDH; severityLevel=8957685N8Y}}
    #>

    $JSONData.result.problems |
    Select-Object -ExpandProperty tagsOfEntities

    # Results

    <#

    #>

    $JSONData.result.problems |
    Select-Object -ExpandProperty ranked

    # Results

    <#
    entityId           entityName severityLevel
    --------           ---------- -------------
    843675746378564876 HGFUTGYJDH 8957685N8Y
    #>