Powershell convertfrom-json | convertto-csv

Question

I have a JSON data structured as following (there may be some mistakes here, the data I'm using is fine):

[{
"id": 12345,
"itemName": "some string",
"sellerId": 123,
"seller": "",
"categoryId": ,
"categoryPath": [
  {
   //more data
  },
  {
   //more data
  }
]}, 
{"id": 12346,
"itemName": "some other string",
"sellerId": 234,
"seller": "",
"categoryId": ,
"categoryPath": [
  {
   //more data
  },
  {
   //more data
  }
]
}]

I would like to convert it to csv so that the selected property names become csv headers and their value (depth 1 only) become data. e.g

id,itemName,sellerId
12345,"some string",123
12346,"some other string",234

I've tried using hundreds of variations of

cat file.json | convertfrom-json | convertto-csv

but none have worked. All I get is csv data with objects names/types and I can't figure out how to make it use only selected properties of each object from json data.

Answer 1

In short you need to do something like this:

(Get-Content file.json -Raw | ConvertFrom-Json) | Select id,itemName,sellerId | Convertto-CSV -NoTypeInformation

The first problem was that Get-Content was passing individual lines to ConvertFrom-Json which is not what it wants. Using the -Raw switch passes it in its entirety.

The (Get-Content file.json -Raw | ConvertFrom-Json) needs to be in parentheses as that allows us to continue with the pipe. The properties are not accessible without doing this. It looks like it is trying to pass the entire object instead of its individual parts down the pipe.

-NoTypeInformation removes lines like this

#TYPE Selected.System.Management.Automation.PSCustomObject