Powershell Object to json array , mimic a Table/CSV

Question

Given any PS object, that might have different number of properties. I need to create a json array of each property values, where the 1st row, would be the name of each property, and subsequent rows the values for each of them.

I thought I could do it pretty easily, but found out the opposite..

let's say my input object is something like this:

$myObj[0..3]

Date_Time           Total_Time  Sub_Time_1 Sub_Time_2
------------------- ----------- ---------- ----------
28/11/2024 00:02:14       67383      14147      12986
28/11/2024 00:03:19       64223       3741      11610
28/11/2024 00:04:15       55636       3577      12246
28/11/2024 00:04:53       37166       2395       6871
...

I want to convert this to a plain json array of arrays.. like this:

[
  ["Date_Time"           ,"Total_Time", "Sub_Time_1", "Sub_Time_2"],
  ["28/11/2024 00:02:14" , 67383      , 14147       , 12986 ], 
  ["28/11/2024 00:03:19" , 64223      , 3741        , 11610 ],
  ["28/11/2024 00:04:15" , 55636      , 3577        , 12246 ],
  ["28/11/2024 00:04:53" , 37166      , 2395        , 6871  ],
...
]

Tryed ConvertTo-Json, but even using -asArray, it creates an Array of json-objects.
[ {}, {} ,... {} ]

But I need array of arrays , with just the values, except the 1st rows which needs to be the name of each "column"

And it needs to be flexible, as the input object may vary, having different property names.

Note: The extra spaces in the json array, was just for readability...

Answer 1

Use a combination of:

• the intrinsic psobject property that PowerShell exposes on objects of any type, to provide reflection on the given object.

• the unary form of , the array constructor operator, in order to emit arrays as a whole to the pipeline.

Piping the result to ConvertTo-Json then yields the desired result.

# Workaround for Windows PowerShell bug affecting to-JSON
# serialization of arrays.
if (-not $IsCoreClr) { Remove-TypeData -ErrorAction Ignore System.Array }

$isHeader = $true
$myObj |
  ForEach-Object {
    if ($isHeader) { $isHeader = $false; , $_.psobject.Properties.Name } 
    $values = $_.psobject.Properties.Value
    , (
        @($values[0].ToString('dd\/MM\/yyyy HH\:mm\:ss')) + 
        $values[1..($values.Count-1)]
      )
  } |
  ConvertTo-Json

Note:

• The solution above works in both Windows PowerShell and PowerShell (Core) 7.

• In Windows PowerShell (the legacy, ships-with-Windows, Windows-only edition of PowerShell whose latest and last version is 5.1), a workaround for a to-JSON serialization bug affecting arrays is needed, Remove-TypeData System.Array, which is explained in this answer.

• Additionally, the code applies explicit string formatting to the [datetime] values in your .Date_Time properties to match your desired output.

  • If you let ConvertTo-Json perform the string conversion, i.e. if you simply used
    , $_.psobject.Properties.Value:

    • PowerShell 7 would use a culture-invariant ISO 8601 string representation that is the equivalent of calling .ToString('o'), e.g. "2024-12-13T13:40:04.815036-05:00"

    • Windows PowerShell would use an obscure custom representation, e.g. "\/Date(1734115307445)\/"

  • See this answer for additional information.