jsonpowershellconvertfrom-json

Powershell ConvertFrom-Json accessing json value


I have a json file that I'd like to convert to csv. However, the actual value is nested inside reading key that I need to access. It returns System.Object[] in which I know I have to access this, but I don't know how.

the following is the result.json file,

{"totalNumberOfReadings":1488,
    "readings":
        [
           {"meterNumber":"xxxxx","date":"2022-02-01 00:00","reading":0.0,"status":"AsRequested"},
           {"meterNumber":"xxxxx","date":"2022-01-31 23:30","reading":0.0,"status":"As Requested"},
           {"meterNumber":"xxxxx","date":"2022-01-31 23:00","reading":0.0,"status":"As Requested"},
           {"meterNumber":"xxxxx","date":"2022-01-31 23:00","reading":0.0,"status":"As Requested"},
        ]
}

My script is like this

C:\> (Get-Content -Raw result.json | ConvertFrom-Json) | ConvertTo-Csv -NoTypeInformation

the output is this

"totalNumberOfReadings","readings"
"1488","System.Object[]"

This is just the summary/metadata. I want the actual content inside the key's value, how to access the value?


Solution

  • Either use member access:

    (Get-Content -Raw result.json | ConvertFrom-Json).readings | ConvertTo-Csv -NoTypeInformation
    

    ...or only pipeline commands:

    Get-Content -Raw result.json | ConvertFrom-Json | ForEach-Object readings | ConvertTo-Csv -NoTypeInformation
    

    ForEach-Object readings is the short form of ForEach-Object -MemberName readings.