Convert a JSON representation of CSV data to actual CSV data

This self-answered question is about transforming a JSON representation of CSV data into actual CSV data.

The following JSON contains separate properties that describe the headers (column names) (columns) and arrays of corresponding row values (rows), respectively:

{
  "columns": [
      {
          "name": "ColumnName1",
          "type": "Number"
      },
      {
          "name": "ColumnName2",
          "type": "String"
      },
      {
          "name": "ColumnName3",
          "type": "String"
      }
  ],
  "rows":    [
      [
          11111,
          "ResourceType1",
          "String1"
      ],
      [
          22222,
          "ResourceType2",
          "String2"
      ],
      [
          33333,
          "ResourceType3",
          "String3"
      ]
  ]
}

How can I convert this JSON input to the CSV data it represents?

Caveat:

• CSV files have no concept of data types - all values are strings, so any data-type information would have to be incorporated in some way as a convention that the consumer of the CSV would have to be aware of.

• The code below does not employ such a convention, meaning that the data-type information from the .column.type properties is lost.

Assume that the JSON in the question is saved in file file.json, which can be parsed into a ([pscustomobject]) object graph with ConvertFrom-Json, via reading the file as text with Get-Content:

# Convert the JSON text into a [pscustomobject] object graph.
$fromJson = Get-Content -Raw file.json | ConvertFrom-Json

# Process the array of column names and the arrays of row values by
# enclosing the array elements in "..." and joining them with ","
(, $fromJson.Columns.Name + $fromJson.Rows).ForEach({
  $_.ForEach({ '"{0}"' -f ($_ -replace '"', '""') }) -join ','
})

Note:

• , $fromJson.Columns.Name + $fromJson.Rows creates a single array of arrays (jagged array), whose first element is the array of column names and each of whose subsequent elements is a data row's column values as follows:

  • , $fromJson.Columns.Name uses the unary form of , the array constructor ("comma") operator to create a single-element array whose only element is the array of column names, obtained via member-access enumeration.

  • +, the addition / string concatenation / array-concatenation operator constructs a new array that is the concatenation of the single-element jagged array with the jagged array stored in $fromJson.Rows

• The above encloses the column names and values in "..." so as to also support names and values with embedded , characters; additionally, embedded " characters, if any, are properly escaped by doubling them.

  • If you know that the input data neither contains values with embedded , nor ", you can simply omit the inner .ForEach() array method call above, i.e. use just
    $_ -join ',', which will result in unquoted values.

The above outputs:

"ColumnName1","ColumnName2","ColumnName3"
"11111","ResourceType1","String1"
"22222","ResourceType2","String2"
"33333","ResourceType3","String3"

• To convert the above in memory to ([pscustomobject]) objects representing the CSV data, use ConvertFrom-Csv (... represents the command above):

  ... | ConvertFrom-Csv
  

• To save the above to a CSV file, use Set-Content; e.g.:

  ... | Set-Content -Encoding utf8 out.csv